SQL Function to Split String into a List

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).

A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.

This explanation is copied from stackoverflow.com to understand the differences of using SQL Functions.

So let's get to the headline. Yes we sometimes need to pass a list of values and get them split from a given delimiter. Especially we can use such function to join with other queries to do the job easier and faster. Look at the following.

To do so, first we maintain a sequence table that has sequential numbers starting from 1.

 CREATE TABLE tblSequence (  
      [id] [int] NOT NULL  
 ) ON [PRIMARY]  
 GO  

And now let's add some sequential numbers to it. For the moment we assume the max would be 8000.

 Declare @i int  
 Select @i = 1  
 While @i <= 8000  
 Begin  
      insert into tblSequence(id) values (@i)  
      if @@error <> 0 Break  
      Select @i = @i + 1  
 End  
 go  

It's time to use this table and create our function.

 CREATE FUNCTION tblListSplit(@ListId varchar(8000),@d char(1))  
 RETURNS TABLE  
 AS  
 RETURN (  
      SELECT  
           row_number() OVER(ORDER BY id) id,  
           NullIf(rtrim(ltrim(SubString(@d + @ListId + @d, id, CharIndex(@d, @d + @ListId + @d, id) - id))), '') Val  
      FROM tblSequence (NOLOCK)  
      WHERE id <= Len(@d + @ListId + @d)  
      AND     SubString(@d + @ListId + @d, id - 1, 1) = @d  
      AND     CharIndex(@d, @d + @ListId + @d, id) - id > 0  
 )  
 GO  

Now the function is ready to serve us as needed. Let's try it.

 select * from tblListSplit('1,2,3,4,5,6,7,8,9,10',',')  

And the result will be like;

So, you can see the list is split according to the delimiter we pass and here we used a comma. Try it with all your needs in your procedures.

Happy Coding..!!!