How to split the result row by comma

Question

I have a question I have a simple table that looks like this when i do select all on it (one column with some rows)

| a, b, c | - 1st row
| b, d, d | - 2nd row
| d, e, f | - 3rd row

Now in trying to split those values by comma so each value would be in separate row something like

|a| - 1st row
|b| - 2nd row
|c| - 3rd row
|d| - 4th row
|e| - 5th row
|f| - 6th row

I was trying with something like:

select id,
case when CHARINDEX(', ', [value])>0 
    then SUBSTRING([value] , 1, CHARINDEX(', ',[value])-1) else [value] end firstname, 
CASE WHEN CHARINDEX(', ', [value])>0 
    THEN SUBSTRING([value],CHARINDEX(', ',[value])+1,len([value])) ELSE NULL END as lastname from table

But it is not the way.

Possible duplicate of [SQL split values to multiple rows](http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) — JohnHC, Jan 11 '17 at 13:39
That looks very much like a bad table design. And instead of fighting it with complicated queries you might want to change it so that you have one value in a database cell instead of a list of comma-separated values. Then querying the data will be much easier. — Thorsten Kettner, Jan 11 '17 at 13:40

John Cappelletti · Accepted Answer · 2017-01-11T14:09:59.843

Without a UDF Parse/Split function

You didn't specify a Table or Column name so replace YourTable and YourList with your actual table and column names.

Select Distinct RetVal
      ,RowNr = Dense_Rank() over (Order by RetVal)
 From  YourTable A
 Cross Apply ( 
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>'+ replace((Select A.YourList as [*] For XML Path('')),',','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
       ) B

Returns

RetVal  RowNr
a       1
b       2
c       3
d       4
e       5
f       6

Using a Split/Parse function (everyone should have a good one)

Select Distinct RetVal
      ,RowNr = Dense_Rank() over (Order by RetVal)
 From  YourTable A
 Cross Apply (Select * from [dbo].[udf-Str-Parse-8K](A.YourList,',') ) B

The UDF -- if interested

CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')

Vamsi Prabhala · Answer 2 · 2017-01-11T13:55:14.800

A recursive cte solution which finds all the ,s in the string and gets the substring between 2 ,s. (Assuming you are on a sql server version 2012+ for lead to work)

with cte as (                       
select val,charindex(',',','+val+',') as location from t
union all
select val,charindex(',',','+val+',',location+1) from cte 
where charindex(',',','+val+',',location+1) > 0
)
,substrings as (select *,
                substring(val,location,
                          lead(location,1) over(partition by val order by location)-location-1) as sub 
                from cte)
select distinct sub 
from substrings 
where sub is not null and sub <> ''
order by 1;

Sample Demo

1) The first cte gets all the , locations in the string recursively. , is appended at the beginning and end of the string to avoid missing the first substring before , and the last substring after ,.

2) For each string, the location of the next , is found using lead ordered by the location of ,.

3) Finally get all those substrings which are not null and are not empty strings.

Gurwinder Singh · Answer 3 · 2017-01-11T14:17:13.193

0

You can do this by using cross apply and XML

select distinct
    p.a.value('.','varchar(10)') col 
from (
    select 
        cast('<x>' + replace(col,', ','</x><x>') + '</x>' as XML) as x
    from your_table) t
    cross apply x.nodes ('/x') as p(a)
) t

edited Jan 11 '17 at 14:17

answered Jan 11 '17 at 13:42

Gurwinder Singh

38,557
6
51
76

be aware of using `cross apply` since it has very bad performance – Matthias Burger Jan 11 '17 at 14:15
1

@MatthiasBurger Then what would your recommendation be? – John Cappelletti Jan 11 '17 at 15:02
@JohnCappelletti just saying... maybe it's not wrong but it's slow af. my recommendation is a better table-design like ThorstenKettner suggested. a better table design (storing one value per cell) has 1. a better performance, 2. doesn't force you to do something like splitting and 3. is the way sql works. Nevertheless, if I had a better solution for splitting I have had suggested it. Once I was in the same situation and ended up creating another table and adding primary- and foreign-keys. – Matthias Burger Jan 11 '17 at 15:32

How to split the result row by comma

3 Answers3