Split function in SQL

I have a scenario in which I have a comma seperted value list which I need to use get data from another table.

so if I can use IN query with this my split function it can work like a charm.


CREATE FUNCTION Split 
( 
 @RowData nvarchar(MAX), 
 @SplitOn nvarchar(50) 
)   
RETURNS @RtnValue table   
( 
 Id int identity(1,1), 
 Data nvarchar(100) 
)   
AS   
BEGIN   
 Declare @Cnt int 
 Set @Cnt = 1 
 
 While (Charindex(@SplitOn,@RowData)>0) 
 Begin 
  Insert Into @RtnValue (data) 
  Select   
   Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) 
 
  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) 
  Set @Cnt = @Cnt + 1 
 End 
   
 Insert Into @RtnValue (data) 
 Select Data = ltrim(rtrim(@RowData)) 
 
 Return 
END

After make this function then we can call like this

DECLARE @listStr Nvarchar(max)
SELECT @listStr = COALESCE(@listStr+',','') +name  FROM A
SELECT @listStr  -- In this i get my values in comma seperted listStr

select Contact_Person,Role_Name from B where B.Name in (select data from dbo.split(@listStr,','))
-- in data it will split out my comma seperted list 

Ref:
http://www.aspforums.net/Threads/646179/Convert-comma-separated-int-values-to-string-values/