Table-valued Functions and List of Values

Often, I needed to transform a list of values into a table-like structure to process the data easier, it was easy to do that in a stored procedure, but data were usable only inside the procedure. Once I jumped in the world of table-valued functions, new horizons opened. One can provide a list of values as parameter to a function and return the values in a tabular form, like in the below example:

CREATE FUNCTION dbo.SplitList( 
    @ListValues varchar(500) 
, @Delimiter char(1)) 
RETURNS @Temp  TABLE(Value varchar(50)) 
AS 
BEGIN 
DECLARE @Index int 
DECLARE @Length int 
SET @Index = CharIndex(@Delimiter, @ListValues) 
SET @Length = Len(@ListValues) - @Index 
   
WHILE @Index > 0 --if the fatch was successful
BEGIN 
INSERT @Temp 
VALUES( Substring(@ListValues, 0, @Index)) 

SET @ListValues = Substring(@ListValues, @Index+1, @Length) 
SET @Index = CharIndex(@Delimiter, @ListValues) 
SET @Length = @Length - @Index 
END  
   
INSERT @Temp 
VALUES(@ListValues) 
RETURN 
END  

And, here are a few simple examples with the function at work:

-- Example 1:
SELECT * FROM dbo.SplitList('24,34,34,56,23', ',')   

 -- Example 2:
SELECT * FROM dbo.SplitList('24 34 34 56 23', ' ')    

-- Example 3: 
SELECT * FROM dbo.SplitList(NULL, ',')

The utility of such a function resides in the fact that it can be used in JOINs or subqueries, to filter or search for values. It can be mixed with tables as well with calls to the same functions:

-- intersection
SELECT A.* 
FROM dbo.SplitList('23,34 50,71', ',') A 
 JOIN dbo.SplitList('23,34,25,45', ',') B 
 ON A.Value = B.Value    

-- A\B
SELECT A.* 
FROM dbo.SplitList('23,34,50,71', ',') A 
 LEFT JOIN dbo.SplitList('23,34,25,45', ',') B 
 ON A.Value = B.Value 
WHERE B.Value IS NULL

-- B\A
SELECT B.* 
FROM dbo.SplitList('23,34,50,71', ',') A 
 RIGHT JOIN dbo.SplitList('23,34,25,45', ',') B 
 ON A.Value = B.Value 
WHERE A.Value IS NULL

-- union
SELECT A.* 
FROM dbo.SplitList('23,34,50,71', ',') A 
UNION 
SELECT *
FROM dbo.SplitList('23,34,25,45', ',') B 

-- carthesian product 
SELECT A.*, B.* 
FROM dbo.SplitList('23,34,50,71', ',') A 
 JOIN dbo.SplitList('23,34,25,45', ',') B 
     ON 1=1

Unfortunately, such JOINs have poor performance when the number of values is increasing. The performance can be improved by declaring the "value" column as PRIMARY KEY, however this won't work with NULL values and duplicates.   Thus by replacing the RETURN line with the following, the queries will perform faster:

RETURNS @Temp TABLE(Value varchar(50) PRIMARY KEY)

Happy coding!