13 September 2007
Table-valued Functions and List of Values
Often I needed to transform a list of values in a table format in order 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. We can provide a list of values as parameter to a function and return the values in a tabular form. I will jump over the introduction of table-valued functions, more information about them can be found in SQL Server Books Online documentation. So, here is the function, as second parameter is provided the delimiter: CREATE FUNCTION dbo.SplitList( @ListValues varchar(500) , @Delimiter char(1)) RETURNS @Temp TABLE(Value varchar(50)) /* Purpose: translates a list of values delimited by @delimiter into a tabular format Parameters: @List varchar(500) - List of values split by @Delimiter , @Delimiter char(1) - Delimiter Notes: comma is used as default delimiter Sample call: SELECT * FROM dbo.SplitList('24,34,34,56,23', ',') SELECT * FROM dbo.SplitList('24 34 34 56 23', ' ') SELECT * FROM dbo.SplitList(NULL, ',') */ 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 It can be tested using the below calls: SELECT * FROM dbo.SplitList('24,34,34,56,23', ',') In this example the list of values is comma delimited, while the next one the values are delimited by a space: SELECT * FROM dbo.SplitList('24 34 34 56 23', ' ') The function works with nulls too: SELECT * FROM dbo.SplitList(NULL, ',') Why is handy such a function? The main utility resides in the fact that it can be used in JOINs or subqueries, to filter or search for values. For example: --using a JOIN SELECT * FROM
JOIN dbo.SplitList('23,34,25,45', ',')
where is just a placeholder for a table.
--using an inner query
WHERE IN (SELECT * FROM dbo.SplitList('23,34,25,45', ','))
We can use even a join between two tables created from list of values. The following query returns the intersection of two lists:
FROM dbo.SplitList('23 34 50 71', ' ') A
JOIN dbo.SplitList('23,34,25,45', ',') B
ON A.Value = B.Value
Of course the JOINs performed on such tables have a low performance, we can speed them by declaring the "value" column as PRIMARY KEY. That's useful when we deal with long lists, however 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)
Now trying to run again the following query:
SELECT * FROM dbo.SplitList('24,34,34,56,23', ',')
Here comes the error:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__SplitList__0F975522'. Cannot insert duplicate key in object 'dbo.@Temp'.
While the following statement:
SELECT * FROM dbo.SplitList(NULL, ',')
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Value', table '@Temp'; column does not allow nulls. INSERT fails.
That's not news, we'll get similar errors when working with normal tables.
I will stop here, more in a next post.