Split comma separated values in sql based on condition

Question

Hi all i am newbie in SQL i have a table in which there is a column named dilution_name in this column there are values coming in comma separated format like A,B,C etc. also these values may vary like in some row the values are A,B,C and in some case its like A,B,C,D i just want to separate these values and print them in multiple column if there is only 3 comma separated values then there should be 3 values in comma would be written rest should be null I have tried

    select ParsedData.* 
    from dilution_table mt
    cross apply ( select str = mt.dilution_name + ',,' ) f1
    cross apply ( select p1 = charindex( ',', str ) ) ap1
    cross apply ( select p2 = charindex( ',', str, p1 + 1 ) ) ap2
    cross apply ( select p3 = charindex( ',', str, p2 + 2 ) ) ap3
    cross apply ( select p4 = charindex( ',', str, p3 + 3 ) ) ap4
    cross apply ( select p5 = charindex( ',', str, p4 + 4 ) ) ap5
    cross apply ( select p6 = charindex( ',', str, p5 + 5 ) ) ap6
    cross apply ( select val1 = substring( str, 1, p1-1 )                   
             , val2 = substring( str, p1+1, p2-p1-1 ),
              val3 = substring( str, p2+1, p2-p1-1 ),
              val4 = substring( str, p3+1, p2-p1-1 ),
              val5 = substring( str, p4+1, p2-p1-1 ),
              val6 = substring( str, p5+1, p2-p1-1 ),
              val7 = substring( str, p6+1, p2-p1-1 )
      ) ParsedData  


            [sample data][1]

sample data

For columns, take a peek at http://stackoverflow.com/questions/40609102/tsql-parse-string-with-4-delimiters/40609526#40609526 — John Cappelletti, May 20 '17 at 13:36
@vkp - I agree this is a dupe, but two quick points. 1) OP wanted columns not rows, and 2) At least point to a set-based splitter, not a multi-statement loop. — John Cappelletti, May 20 '17 at 13:41

SqlZim · Answer 1 · 2017-05-20T13:19:15.697

In SQL Server 2016+ you can use string_split() (though it has no ordinal number).

In SQL Server pre-2016, using a CSV Splitter table valued function by Jeff Moden:

declare @str varchar(128) = 'a,b,c,d'

select s.ItemNumber, s.Item
from dbo.delimitedsplit8k(@str,',') s;

rextester demo: http://rextester.com/EGZ24917

returns:

+------------+------+
| ItemNumber | Item |
+------------+------+
|          1 | a    |
|          2 | b    |
|          3 | c    |
|          4 | d    |
+------------+------+

To pivot the data after splitting, you can use conditional aggregation like so:

select 
    v1 = max(case when s.ItemNumber = 1 then s.Item end)
  , v2 = max(case when s.ItemNumber = 2 then s.Item end)
  , v3 = max(case when s.ItemNumber = 3 then s.Item end)
  , v4 = max(case when s.ItemNumber = 4 then s.Item end)
  , v5 = max(case when s.ItemNumber = 5 then s.Item end)
from dbo.delimitedsplit8k(@str,',') s;

returns:

+----+----+----+----+------+
| v1 | v2 | v3 | v4 |  v5  |
+----+----+----+----+------+
| a  | b  | c  | d  | NULL |
+----+----+----+----+------+

splitting strings reference:

i did not want split values and print in rows i just want if there are 5 comma separated values then 5 column horizontal be visible and if there are 4 comma separated values then 4 values should be there and 5th column would be null — shravan pal, May 20 '17 at 13:16
@shravanpal My mistake, updated to include conditional aggregation form of pivot after splitting. — SqlZim, May 20 '17 at 13:19
@shravanpal Add example data and desired results to your question — SqlZim, May 22 '17 at 12:24
sir i have solved the problem your couple of codes help me to solve my problem any ways thanks. — shravan pal, May 23 '17 at 06:14

Split comma separated values in sql based on condition

1 Answers1