SQL Split String into Table

December 27, 2005 by pleinolijf 11 Comments

If you ever needed to split a delimited string into a table of rows, here’s what you need.

In the example I needed to join all the values (which were actually keyfields) in the string with an existing table. This function was exactly what I needed.

[ do pardon the crappy layout, this is the best I could do in a short time ]

CREATE FUNCTION iter_charlist_to_table

(@list      ntext,

@delimiter nchar(1) = N',')

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str     varchar(4000),

nstr    nvarchar(2000)) AS BEGIN

DECLARE @pos      int,

@textpos  int,

@chunklen smallint,

@tmpstr   nvarchar(4000),

@leftover nvarchar(4000),

@tmpval   nvarchar(4000)

SET @textpos = 1

SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2

SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0

BEGIN

SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))

INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)

SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

SET @pos = charindex(@delimiter, @tmpstr)

END

SET @leftover = @tmpstr

END

INSERT @tbl(str, nstr)

VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))

RETURN

END

And a version for a list of integers (faster; space-delimited):

CREATE FUNCTION iter_intlist_to_table

(@list ntext)

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

number  int NOT NULL) AS

BEGIN

DECLARE @pos      int,

@textpos  int,

@chunklen smallint,

@str      nvarchar(4000),

@tmpstr   nvarchar(4000),

@leftover nvarchar(4000)  SET @textpos = 1

SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2

SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))

SET @textpos = @textpos + @chunklen

SET @pos = charindex(' ', @tmpstr)

WHILE @pos > 0

BEGIN

SET @str = substring(@tmpstr, 1, @pos - 1)

INSERT @tbl (number) VALUES(convert(int, @str))

SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))

SET @pos = charindex(' ', @tmpstr)

END

SET @leftover = @tmpstr

END

IF ltrim(rtrim(@leftover)) <> ''

INSERT @tbl (number) VALUES(convert(int, @leftover))

RETURN

END

You simply create these two user defined functions in SQL Server, and they are ready to use like this:

select @total = sum(t.invoiced)
from invoices t
-- function to split a ";"-delimited string
inner join iter_charlist_to_table(@budgetid, ';') i on i.[str] = t.budgetid

The resulting table of the charlist function will contain two columns: “str” -the value itself-, and a “number” column -just an autonumber-.
The intlist result will be a table with “listpos”, and “number”, the latter being the int value in the list. A bit confusing between the two functions, I know. But it’s easy to rename them yourself 😉

My source was this site (Erland Sommarskog).