split comma separated values into distinct rows

Question

I have a table which looks like this:

id  fk_det  userid
3   9   name1,name2
6   1   name3
9   2   name4,name5
12  3   name6,name7

I have already learned to regret having the values of the userid in comma separated values, so i want to split the rows up and end up with something that looks like

id  fk_det  userid
3   9   name1
x   9   name2
6   1   name3
9   2   name4
x   2   name5
12  3   name6
x   3   name7

i had been looking at something like this:

select fk_det, det, LEFT(userid, CHARINDEX(',',userid+',')-1),
    STUFF(userid, 1, CHARINDEX(',',userid+','), '')
from global_permissions

but i'm not sure how to make it work when userid contains more than 2 items (which it might, some might have none, some might have multiple, just depends)

possible duplicate of [How do I expand comma separated values into separate rows using SQL Server 2005?](http://stackoverflow.com/questions/702968/how-do-i-expand-comma-separated-values-into-separate-rows-using-sql-server-2005) — MPelletier, Oct 24 '11 at 22:30
Possible solution http://stackoverflow.com/questions/6418214/table-normalization-parse-comma-separated-fields-into-individual-records — Alex Aza, Oct 12 '12 at 18:18
duplicate of http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — Gangnus, Dec 11 '12 at 10:04

score 1 · Answer 1 · edited Nov 13 '18 at 22:55

this is what I tend to use:

IF EXISTS (
    SELECT 1
    FROM dbo.sysobjects
    WHERE id = object_id(N'[dbo].[ParseString]')
        AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
    DROP FUNCTION [dbo].[ParseString]
END
GO

CREATE FUNCTION dbo.ParseString (@String VARCHAR(8000), @Delimiter VARCHAR(10))
RETURNS TABLE
AS
/*******************************************************************************************************
*    dbo.ParseString
*
*    Creator:        magicmike
*    Date:           9/12/2006
*
*
*    Outline:        A set-based string tokenizer
*                    Takes a string that is delimited by another string (of one or more characters),
*                    parses it out into tokens and returns the tokens in table format.  Leading
*                    and trailing spaces in each token are removed, and empty tokens are thrown
*                    away.
*
*
*    Usage examples/test cases:
                Single-byte delimiter:
                     select * from dbo.ParseString2('|HDI|TR|YUM|||', '|')
                     select * from dbo.ParseString2('HDI| || TR    |YUM', '|')
                     select * from dbo.ParseString2(' HDI| || S P A C E S |YUM | ', '|')
                     select * from dbo.ParseString2('HDI|||TR|YUM', '|')
                     select * from dbo.ParseString2('', '|')
                     select * from dbo.ParseString2('YUM', '|')
                     select * from dbo.ParseString2('||||', '|')
                     select * from dbo.ParseString2('HDI TR YUM', ' ')
                     select * from dbo.ParseString2(' HDI| || S P A C E S |YUM | ', ' ') order by Ident
                     select * from dbo.ParseString2(' HDI| || S P A C E S |YUM | ', ' ') order by StringValue

                Multi-byte delimiter:
                     select * from dbo.ParseString2('HDI and TR', 'and')
                     select * from dbo.ParseString2('Pebbles and Bamm Bamm', 'and')
                     select * from dbo.ParseString2('Pebbles and sandbars', 'and')
                     select * from dbo.ParseString2('Pebbles and sandbars', ' and ')
                     select * from dbo.ParseString2('Pebbles and sand', 'and')
                     select * from dbo.ParseString2('Pebbles and sand', ' and ')
*
*
*    Notes:
                     1. A delimiter is optional.  If a blank delimiter is given, each byte is returned in it's own row (including spaces).
                        select * from dbo.ParseString3('|HDI|TR|YUM|||', '')
                     2. In order to maintain compatibility with SQL 2000, ident is not sequential but can still be used in an order clause
                     If you are running on SQL2005 or later
                        SELECT Ident, StringValue FROM
                     with
                        SELECT Ident = ROW_NUMBER() OVER (ORDER BY ident), StringValue FROM
*
*
*    Modifications
*
*
********************************************************************************************************/
RETURN (
SELECT Ident, StringValue FROM
    (
        SELECT Num as Ident,
            CASE
                WHEN DATALENGTH(@delimiter) = 0 or @delimiter IS NULL
                    THEN LTRIM(SUBSTRING(@string, num, 1)) --replace this line with '' if you prefer it to return nothing when no delimiter is supplied. Remove LTRIM if you want to return spaces when no delimiter is supplied
            ELSE
                LTRIM(RTRIM(SUBSTRING(@String,
                    CASE
                        WHEN (Num = 1 AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) <> @delimiter) THEN 1
                        ELSE Num + DATALENGTH(@delimiter)
                    END,
                    CASE CHARINDEX(@Delimiter, @String, Num + DATALENGTH(@delimiter))
                        WHEN 0 THEN LEN(@String) - Num + DATALENGTH(@delimiter)
                        ELSE CHARINDEX(@Delimiter, @String, Num + DATALENGTH(@delimiter)) - Num -
                            CASE
                                WHEN Num > 1 OR (Num = 1 AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) = @delimiter)
                                       THEN DATALENGTH(@delimiter)
                                ELSE 0
                            END
                       END
                    )))
              End  AS StringValue
        FROM dbo.Numbers
        WHERE Num <= LEN(@String)
            AND (
                    SUBSTRING(@String, Num, DATALENGTH(ISNULL(@delimiter,''))) = @Delimiter
                    OR Num = 1
                    OR DATALENGTH(ISNULL(@delimiter,'')) = 0
                )
    ) R WHERE StringValue <> ''
)

You would use it like this:

SELECT id, pk_det, V.StringValue as userid
FROM myTable T
OUTER APPLY dbo.ParseString(T.userId) V

The UDF requires a 'tally' or Number table which assumes the following schema:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Numbers')
BEGIN

     CREATE TABLE dbo.Numbers
    (
        Num INT NOT NULL 
        CONSTRAINT [PKC__Numbers__Num] PRIMARY KEY CLUSTERED (Num) on [PRIMARY]
    )
    ;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
          Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
          Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
          Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
          Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

    INSERT INTO dbo.Numbers(Num)
    SELECT n
    FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
          FROM Nbrs ) D ( n )
         WHERE n <= 50000 ;
END

Numbers tables are an invaluable addition to your toolset. To quote Adam Machanic:

Numbers tables are truly invaluable. I use them all of the time for string manipulation, simulating window functions, populating test tables with lots of data, eliminating cursor logic, and many other tasks that would be incredibly difficult without them.

Is using a table of numbers a hack, as I've seen some people claim? No. Show me another way to efficiently do all of the things a numbers table can. Does it waste space? No. The script below will use up around 900 KB of disk space in each database. That's absolutely nothing. You'll end up getting millions, maybe billions of times the disk space investment back in terms of ease of development and time saved.

http://dataeducation.com/you-require-a-numbers-table/

score 1 · Answer 2 · answered Oct 27 '11 at 13:45

Try this :)

DECLARE @Name TABLE
    (
      id INT NULL ,
      fk_det INT NULL ,
      userid NVARCHAR(100) NULL
    )

INSERT INTO @Name
                      ( id,  fk_det, userid)
VALUES     (3,9,'name1,name2'  )

INSERT INTO @Name
                      ( id,  fk_det, userid)
VALUES     (6,1,'name3'  )

INSERT INTO @Name
                      ( id,  fk_det, userid)
VALUES     (9,2,'name4,name5'  )

INSERT INTO @Name
                      ( id,  fk_det, userid)
VALUES     (12,3,'name6,name7'  )

SELECT  *
FROM    @Name

 SELECT id,A.fk_det,  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT id,fk_det,  
         CAST ('<M>' + REPLACE(userid, ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  @Name) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

Simple and functional. Some cases, will be need TRIM functions, as comma values tend to be separated by " ,",etc.. — Andre Figueiredo, Oct 28 '13 at 16:36

score 0 · Answer 3 · answered Oct 25 '11 at 01:22

As an alternative to the standard sproc call you see everywhere:

with temp as(
select id,fk_det,cast('<comma>'+replace(userid,',','</comma><comma>')+'</comma>' as XMLcomma
from global_permissions
)

select id,fk_det,a.value('comma[1]','varchar(512)')
cross apply temp.XMLcomma.nodes('/comma') t(a)

split comma separated values into distinct rows

3 Answers3

Linked