List to Table Function

22 Sep 2008

A very common task for database developers taking reigns of an existing project is to normalize data. I needed a convenient way to split delimited values into a table for transformation purposes. After finding a wonderful algorithm, I decided to enhance it and transform it to a User-Defined Function in the process. Be aware that the function depends on a Tally table to exist – much like you might have with a Calendar table to join against.

To start, instantiate dbo.Tally:

CREATE TABLE dbo.Tally (ID INT PRIMARY KEY)
DECLARE @a INT
SET @a = 1
WHILE (@a <= 8000)
BEGIN
	INSERT INTO dbo.Tally VALUES(@a)
	SET @a = @a + 1
END

Define the actual UDF:

CREATE FUNCTION dbo.f_ListToTable
(
	    @Items VARCHAR(MAX)
	  , @Delimiter VARCHAR(5) = ','
)
RETURNS @OutputItems TABLE (Item VARCHAR(MAX))
AS
BEGIN
	--Original idea from http://sqlblog.com/blogs/peter_debetta/archive/2004/02/16/33.aspx
	--Accessed 2008-09-22
	DECLARE
		@InputItems TABLE (Item VARCHAR(MAX))
 
	INSERT INTO @InputItems VALUES (@Items)
 
	INSERT INTO
		@OutputItems
	SELECT
		NULLIF(SUBSTRING(@Delimiter + Item + @Delimiter , ID, CHARINDEX(@Delimiter , @Delimiter + Item + @Delimiter , ID) - ID) , '')
	FROM 
		dbo.Tally
	INNER JOIN
		@InputItems ON ID <= LEN(@Delimiter + Item + @Delimiter)
			AND  SUBSTRING(@Delimiter + Item + @Delimiter , ID - LEN(@Delimiter), LEN(@Delimiter)) = @Delimiter
			AND  CHARINDEX(@Delimiter , @Delimiter + Item + @Delimiter , ID) - ID > 0
 
	RETURN
END

And some test cases:

SELECT * FROM dbo.f_ListToTable('A,rose,by,any,other,name,smells,just,as,sweet', DEFAULT)
SELECT * FROM dbo.f_ListToTable('Bill Gates;|Jackie Robinson;|Lisa Simpson;|Jeff Bridges;|', ';|')

And the results:

Item
--------------------
A
rose
by
any
other
name
smells
just
as
sweet
 
(10 row(s) affected)
 
Item
--------------------
Bill Gates
Jackie Robinson
Lisa Simpson
Jeff Bridges
 
(4 row(s) affected)

Notes:

Be aware that I’ve changed VARCHAR(8000) to VARCHAR(MAX). If you’re running SQL Server 2000, you’ll need to revert back.
If you plan on having more than 8000 items in a list, insert additional rows into dbo.Tally.

Tags: function, HOWTO, SQL Server, UDF

shiva ramani: good instruction. How to push data to MySQL from SQL 2005 [...]

Winkey: I've created a linked server for mysql successfully, and also can do Insert, Select. But when I try [...]

RaghuRam: Thanks a lot buddy it helped me to change the root password when I got the error trying to c [...]

Taylor Gerring: If you're on a 64-bit OS, did you run the 32-bit version of ODBC applet? Run: odbcad32 [...]

Taylor Gerring: If the issue is the guest sync'ing to the host, can't you just force time sync the host if you can't [...]

Idea Excursion

List to Table Function

Comment Form

Categories

Archives

Blogroll

Other