Get the value of a column replacing the comma separator

Question

How can I get each value of a column that has a comma separator in her value ?

Example:

ID   ColumnUnified
1    12,34,56,78
2    80,99,70,56

What I want is a query to get the number without comma. If possible, in collumns.

12    34     56    78

Your database structure is violationg the first normalisation rule. You are better off redesigning your DB. — juergen d, Jul 27 '12 at 13:00
There is a similar question here..... http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql — Tom B, Jul 27 '12 at 13:00

score 0 · Accepted Answer · answered Jul 27 '12 at 13:04

0

This will work for any number of values http://beyondrelational.com/modules/2/blogs/70/posts/10844/splitting-delimited-data-to-columns-set-based-approach.aspx

answered Jul 27 '12 at 13:04

Madhivanan

13,470
1
24
29

score 0 · Answer 2 · edited May 23 '17 at 10:34

The solution Madhivanan's link refers to is very creative, but I had a slight problem with it on SQL Server 2012 related to the name of one of the columns (Start). I've modified the code in his answer to use StartPos instead of Start for the column name.

I was not familiar with the system procedure spt_values, but I found a very informative description of the procedure here on SO for those who are interested in exactly how this solution works.

Finally, here's the (slightly) revised code from Madhivana's answer:

CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns')

DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT  @pivot = COALESCE(@pivot + ',', '') + '[col'
        + CAST(number + 1 AS VARCHAR(10)) + ']'
FROM    master..spt_values
WHERE   type = 'p'
        AND number <= ( SELECT  MAX(LEN(data) - LEN(REPLACE(data, ',', '')))
                        FROM    #test
                      )

SELECT  @select = '
    select p.*
    from (
    select
    id,substring(data, StartPos+2, endPos-StartPos-2) as token,
    ''col''+cast(row_number() over(partition by id order by StartPos) as varchar(10)) as n
    from (
    select
    id, data, n as StartPos, charindex('','',data,n+2) endPos
    from (select number as n from master..spt_values where type=''p'') num
    cross join
    (
    select
    id, '','' + data +'','' as data
    from
    #test
    ) m
    where n < len(data)-1
    and substring(data,n+1,1) = '','') as data
    ) pvt
    Pivot ( max(token)for n in (' + @pivot + '))p'

EXEC(@select)

DROP TABLE #test

Get the value of a column replacing the comma separator

2 Answers2