Room of a pleinolijf

Ask yourself this: how do I want to be remembered ?

SQL Split String into Table

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).

Advertisements

Author: pleinolijf

father | volleyballer | software engineer

11 thoughts on “SQL Split String into Table

  1. Doesn’t work.

    Like

  2. Then you’re probably doing something wrong, because I’m using it for a project that’s in production.
    In the example above, make sure the input string (@_budgetid) looks like “(1234;4321;9999;1111)” (without the quotation marks. This will result in a table with four rows containing one of the four numbers in each row.
    You can find more info by following the link of my source.

    Good luck!

    Like

  3. I get this error:
    Server: Msg 155, Level 15, State 1, Line 3
    ‘career_id’ is not a recognized OPTIMIZER LOCK HINTS option.

    SQL:
    SELECT search_id, candidate_id, career_id, careerInterest.Label
    FROM personal_search
    INNER JOIN iter_charlist_to_table(career_id, ‘,’) WITH nolock ON i.[str] = careerInterest.Career_ID

    Please Help.

    Like

  4. Joshua, you have to supply a string variable in place of career_id, containing some delimited string values.
    I think you are trying to cross-link each record of the personal_search table, right? In that case, you should construct some sort of loop (but try to avoid cursors, unless you don’t care for performance), in which you recursively inject the career_id into the user function.

    Hope that clears things up for you…

    Like

  5. Josh, it works. Both functions work. Thanks for the code Muzik ^^

    Like

  6. Thanks. It works great in my solution.

    Like

  7. Works Just awesome!! Thanks

    Like

  8. Fabulous! It works like a charm. Thanks a million!

    Like

  9. For you ignorant readers:
    On line 10 in the 1st example, “ASBEGIN” should be “AS BEGIN” or
    “AS
    BEGIN”

    figure it out!

    Like

  10. Oops, never saw that one 🙂
    Thanks for the notice, I have corrected the code in this post.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s