Split String Function

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Comments posted to this topic are about the item Split String Function

  • smiller 16120

    SSC Enthusiast

    Points: 170

    This is a nice function but I don't see any recursion, at least not as I understand it. What exactly do you mean?

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    The recursion comes in with the multiple executions of the SUBSTRING function. By using the Numbers table, or the tally table generated on the fly depending on your implementation, we are able to walk through the string, character by character, executing the SUBSTRING function along the way to find our delimiter and parse out the value we want to output to the caller. Recursion can be a source of hidden RBAR (Row By Agonizing Row), but tends to be lighter weight than a WHILE LOOP or CURSOR.

  • smiller 16120

    SSC Enthusiast

    Points: 170

    I see. I'm not sure I'd call that recursion, but it's a neat trick.

    Here's another approach using a recursive CTE. I haven't coded it up as a function or catered for text qualified separators, but you get the idea:

    DECLARE @String VARCHAR(255) = '1,2,345,6,yyr';

    DECLARE @Separator VARCHAR(1)= ',';

    WITH CTE

    AS ( SELECT 1 AS PartNo ,

    SUBSTRING(@String + @Separator, 1,

    CHARINDEX(@Separator, @String + @Separator,

    1) - 1) AS Part ,

    SUBSTRING(@String,

    CHARINDEX(@Separator, @String + @Separator,

    1) + 1,

    LEN(@String + @Separator)

    - CHARINDEX(@Separator, @String + @Separator,

    1)) AS Remainder

    UNION ALL

    SELECT PartNo + 1 AS PartNo ,

    SUBSTRING(Remainder + @Separator, 1,

    CHARINDEX(@Separator, Remainder + @Separator,

    1) - 1) AS Part ,

    SUBSTRING(Remainder,

    CHARINDEX(@Separator, Remainder + @Separator,

    1) + 1,

    LEN(Remainder + @Separator)

    - CHARINDEX(@Separator,

    Remainder + @Separator, 1)) AS Remainder

    FROM CTE

    WHERE Remainder <> ''

    )

    SELECT PartNo ,

    Part

    FROM CTE

    (Apologies, this was beautifully formatted before I c&ped from SSMS)

  • smiller 16120

    SSC Enthusiast

    Points: 170

    A bit of Googling around this topic led me to this article, which is pretty comprehensive:

    http://sqlperformance.com/2012/07/t-sql-queries/split-strings

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    That is a great article. I hadn't read that before, but it definitely brings the performance of different approaches to light.

  • pazitzik

    Valued Member

    Points: 59

    I recommend on this function(much better performance,Do it by XML query):

    CREATE FUNCTION dbo.uf_SplitStringsToTable_XML

    (

    @List NVARCHAR(MAX),

    @Delimiter NVARCHAR(255)

    )

    RETURNS TABLE

    AS

    RETURN

    (

    SELECT Data = y.i.value('(./text())[1]', 'INT')

    FROM

    (

    SELECT x = CONVERT(XML, '<i>'

    + REPLACE(@List, @Delimiter, '</i><i>')

    + '</i>').query('.')

    ) AS a CROSS APPLY x.nodes('i') AS y(i)

    );

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Yes. That is better preforming as long as no XML reserved words are used.

    https://technet.microsoft.com/en-us/library/ms145315%28v=sql.90%29.aspx

  • ksatpute123

    Hall of Fame

    Points: 3325

    -- Tally Table approach (No R-BAR optimal for millions of rows.)

    CREATE FUNCTION udf_splitString

    (

    @STR NVARCHAR(MAX),

    @sep NCHAR(1)

    )

    RETURNS TABLE

    AS

    RETURN(

    WITH cteTally(POS)

    AS

    (

    SELECT TOP (LEN(ISNULL(@sep + @STR + @sep, 0))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM SYS.COLUMNS a CROSS APPLY SYS.COLUMNS b

    )

    SELECT SUBSTRING(@sep + @STR + @sep, POS + 1, CHARINDEX(@sep, @sep + @STR + @sep, POS + 1) - POS -1) [Value]

    FROM cteTally

    WHERE POS <= LEN(@sep + @STR + @sep) - 1

    AND SUBSTRING(@sep + @STR + @sep, POS, 1) = @sep

    );

    I think this a much cleaner and simple approach towards achieving the same result. Please test it on different sets and let me know if any changes are needed.

  • ksatpute123

    Hall of Fame

    Points: 3325

    Here are a few other approaches and there impacts:

    -- Recursive CTE approach (Hidden R-BAR optimal for thousands to few hundred thousand rows.)

    WITH cte_Split_String

    AS

    (

    SELECT CAST(0 AS BIGINT) AS idx1,CHARINDEX(@sep,@str) idx2

    UNION ALL

    SELECT idx2 + 1,CHARINDEX(@sep,@str,idx2+1)

    FROM cte_Split_String

    WHERE idx2>0

    )

    INSERT INTO @value

    SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) [value]

    FROM cte_Split_String

    OPTION (MAXRECURSION 0)

    -- XML transform approach (Transform overhead and delimiter restrictions)

    DECLARE @xml XML = (SELECT CONVERT(XML,'<r>' + REPLACE(@str,@sep,'</r><r>') + '</r>'))

    INSERT INTO @value(Value)

    SELECT t.value('.','NVARCHAR(MAX)')

    FROM @xml.nodes('/r') AS x(t)

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1172

    Also take a look at this article Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]

  • macintyre_bernie

    SSC Enthusiast

    Points: 122

    Probably the best you will find out there: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good function, thanks.

  • macintyre_bernie

    SSC Enthusiast

    Points: 122

    I would suggest looking at Jeff Moden's function

    Tally OH! An Improved SQL 8K “CSV Splitter” Function at http://www.sqlservercentral.com/articles/Tally+Table/72993/

  • AVX

    Old Hand

    Points: 391

    unComplicate:

    create table #tmp (x nvarchar(max))

    declare @S nvarchar(max) = 'a,1,2,x,3,4,z'

    declare @aux nvarchar(max)

    while charindex(',',@s) > 0

    begin

    set @aux = substring(@s,0,charindex(',',@s))

    SET @S = LTRIM(STUFF(@s,1,len(@aux)+1,''))

    insert into #tmp select @aux

    end

    insert into #tmp select @S

    select x from #tmp

    drop table #tmp

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply