Many times we have faced this problem: we have an array of IDs in our program that we want to pass as a parameter to a stored procedure. In the stored procedure we would like to use this data, only we wish if it would have been passed as a table.
Yesterday I was trying to answer a post where I found myself in a similar situation, so I decided to write this.
Converting a string to table has been done before with the help of while loops inside a function. Instead of using the loop, I decided to use a recursive CTE , to show how this can be achieved. In this example we need to go from this string:
to this table:
Here is the function that will do this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Amit Gaur -- Create date: July 25th 2008 -- Description: Convert a string to a table -- ============================================= CREATE FUNCTION [dbo].[strToTable] ( @array varchar(max), @del char(1) ) RETURNS @listTable TABLE ( item int ) AS BEGIN WITH rep (item,list) AS ( SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item, SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list UNION ALL SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item, SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list FROM rep WHERE LEN(rep.list) > 0 ) INSERT INTO @listTable SELECT item FROM rep RETURN END GO
How to call the function:
DECLARE @array VARCHAR(max) SET @array = '1,2,4,8' SELECT item FROM strToTable(@array,',')
This function uses a recursive CTE. For people who don't know what this is, basically it calls itself and reuses the result set until it is empty. to do this we have to define basic query and recursive query The basic query is:
SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item, SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list
This query will give us two things: the part of the string that is before the delimiter and part of the string that is after the delimite. The latter string constitutes the left over string as we start from position 1.
The recursive query is:
SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item, SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list FROM rep WHERE LEN(rep.list) > 0
This is the same query except that instead of using original string, @array, which was passed in, we are using the list from the CTE rep result set. On consecutive iterations the rep's values will change as the left over string changes.
It will changes like this:
1,2,4,8 (original as passed in)
I hope this was helpful. It gives you an easy way to turn a string into a table.