Introduction

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.

Solution

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:

'1,2,4,8'

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,',')

Breakdown

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)

2,4,8

4,8

8

I hope this was helpful. It gives you an easy way to turn a string into a table.