Convert a comma separted list to a table in SQL

I thought of sharing this simple function that you can use for splitting a list or nvarchar(max) or anything that is coma separated and sent to DB, into a temp table.

Reason:

Performance is a bottle neck for us. As for that matter, for any SP executions. We cannot live with the IN operator. It is always advicable to go ahead with SET based operations like JOINs in these cases. They also have a good track record of showing an improved performance to a greater extend.

If you need to have such a list in a table format, it is always advicable to have a generic function in your DB to handle such operations, as you might have such scenarios coming up in other SPs also.

Adding the SplitList Function to fulfil the above requirement is shared below:

CREATE FUNCTION [dbo].[SplitList]   
(   
 @List nvarchar(max),   
 @SplitOn nvarchar(5)   
)     
RETURNS @RtnValue table    
(   
     
 Id int identity(1,1),   
 Value nvarchar(100)   
)    
AS     
BEGIN   
   
While (Charindex(@SplitOn,@List)>0)   
Begin    
Insert Into @RtnValue (value)   
Select    
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))    
    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))   
End    
   
Insert Into @RtnValue (Value)   
    Select Value = ltrim(rtrim(@List))   
    Return   
END 

–declare @tempList nvarchar(max)
–set @tempList = ‘1,2,3,4,5,6,7,8,9,10’

–select VALUE FROM dbo.SplitList(@tempList,’,’) — Remember, this will give you a table.. so store this statement into a temptable

 

Run the above function in your DB and you can start using the split list operation. Check the last 3 statements to see the usage of this function.

You normally should do

INSERT INTO @tbl_MyTempTable (ListValue)

SELECT VALUE FROM dbo.SplitList(@tempList,’,’) — my list is a coma separated one..

The above @tbl_MyTempTable will have my list (‘1,2,3,4,5’) stored as each rows..

Hope this was helpful…