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!
No comments:
Post a Comment