SQL Server custom SplitString function with several splits.

Question

Hey peeps.

Thanks in advance!

I have a Function in SQL server 2008 which splits a string based on the char i give. Lovely and simple. When I pass in:

Content1,Content2,Content3

I can create a lovely temp table with those values in a single column split by the comma (,).

Now, that's all good, but I now need to split the following into a table, but with 2 columns. "Name" and "Order"

Content1:1,Content2:2,Content3:3 ...

Anyone got anything to do this? Or a better way to pass the data in? If not, i'm going to either run a separate stored proc for each of the items. I would rather not. But if I have to, I have to! Or, create a temp table from the temp table.. Messy i'd say!

Thanks again, in advance. James

Answer 1

not sure what your original split function is like, but i can't image it would be hard to extend it.

CREATE FUNCTION [dbo].[mysplit] ( @input nvarchar(MAX), @row_delimiter char(1), @col_delimiter char(1))
RETURNS @OutputTable TABLE ( [name] nvarchar(100), [order] nvarchar(100) )
AS
BEGIN

    declare 
      @str1 nvarchar(100)
    , @str2 nvarchar(100)
    , @tmpstr nvarchar(200)

    WHILE LEN(@input) > 0
    BEGIN
        set @tmpstr = LEFT(@input, ISNULL(NULLIF(CHARINDEX(@row_delimiter, @input) - 1, -1), LEN(@input)))                        
        set @str1 = LEFT(@tmpstr, ISNULL(NULLIF(CHARINDEX(@col_delimiter, @tmpstr) - 1, -1), LEN(@tmpstr)))
        set @str2 = SUBSTRING(@tmpstr,ISNULL(NULLIF(CHARINDEX(@col_delimiter, @tmpstr), 0), LEN(@tmpstr)) + 1, LEN(@tmpstr)) 
        set @input = SUBSTRING(@input,ISNULL(NULLIF(CHARINDEX(@row_delimiter, @input), 0), LEN(@input)) + 1, LEN(@input))
        INSERT INTO @OutputTable ( [name] , [order] )
        VALUES ( @str1 , @str2)
    END

    RETURN
END
GO

select * from mysplit('Content1:1,Content2:2,Content3:3', ',' , ':')