Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Convert String to a Table using CTE

By Amit Gaur,

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.

Total article views: 12763 | Views in the last 30 days: 11
 
Related Articles
FORUM

substring

using substring to parse data from string

FORUM

SUBSTRING Question?

SUBSTRING

FORUM

Arrays

Array query?

FORUM

same string selection

same string selection

FORUM

Array in SQL server

Array in SQL server

Tags
cte    
ids to table    
sql server 2005    
string to table    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones