Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: normalising comma separated string to multiple records

    Hi,

    I need to normalise comma separated strings of tags (SQL Server 2008 R2).
    eg (1, 'abc, DEF, xyzrpt') should become
    (1, 'abc')
    (1, 'DEF')
    (1, 'xyzrpt')

    I have written a procedure in T-SQL that can handle this. But it is slow and it would be better if the solution was available as a view, even a slow view would be better.

    I remember having seen solutions to deal with this kind of problem on this forum, but I can't find any of those. No matter my search criteria. Most solutions I found go the way round: from (1, 'abc'), (1, 'DEF') and (1, 'xyzrpt'), generate (1, 'abc, DEF, xyzrpt').

    If memory serves, it used "FOR XML PATH". But it's been a while and I may be totally wrong.
    Last edited by Wim; 10-17-12 at 11:35. Reason: added "SQL Server 2008 R2"
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I use this:
    Code:
    create function ParseString(@String varchar(500), @Delimiter char(1))
    returns table
    as
    
    --function ParseString
    --blindman, 5/20/2008
    --Parses a string based upon a given single-character delimiter,
    --without using loops or a tally table.
    
    --test parameters
    --declare	@String varchar(500)
    --declare	@Delimiter char(1)
    --set		@String = 'rnbqkbnr/pp1ppppp/8/2p5/4P3/5N2/PPPP1PPP/RNBQKB1R'
    --set		@Delimiter = '/'
    --set		@String = 'Abracadabra!, Shazam!, Presto!'
    --set		@Delimiter = ','
    --;
    
    return
    (
    with Results as
    		(select	1 as Ordinal,
    				ltrim(left(@String, charindex(@Delimiter, @String + @Delimiter)-1)) as StringValue,
    				convert(varchar(500), right(@String + @Delimiter, len(@String) - charindex(@Delimiter, @String+@Delimiter) + 1)) as Remaining
    		UNION ALL
    		select	Ordinal+1,
    				ltrim(left(Remaining, charindex(@Delimiter, Remaining)-1)),
    				right(Remaining, len(Remaining) - charindex(@Delimiter, Remaining))
    		from	Results
    		where	len(Remaining) > 0)
    select	Ordinal,
    		StringValue
    from	Results
    )
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Blindman, thank you for your reply.

    My first test show the performance is better than what I had. I will do a full test later.

    Thank you.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •