SQL - Splitting string in multiple columns

Question

I'd like to split the result from a query and display the values in separate columns. As an example I get the following result

|Name          |
|ABC_DEFG_HIJKL|
|A_B_C         |
|A_B_C_D       |

I want to split the values by '_' and add them to separate columns. The query result should look something like this

|Name          |first   |second   |third   |fourth|
|ABC_DEFG_HIJKL|ABC     |DEFG     |HIJKL   |null  |
|A_B_C         |A       |B        |C       |null  |
|A_B_C_D       |A       |B        |C       |D     |

So far I can split the result. But for each value, I have a new row. So I just need to merge the results into one row and make a column for each of those rows.

SELECT DP.Name, value  
FROM RitopDatenpunkt DP  
    CROSS APPLY STRING_SPLIT(DP.Name, '_'); 


|Name          |value   |
|ABC_DEFG_HIJKL|ABC     |
|ABC_DEFG_HIJKL|DEFG    |
|ABC_DEFG_HIJKL|HIJKL   |
|A_B_C         |A       |
|A_B_C         |B       |
|A_B_C         |C       |
|A_B_C_D       |A       |
|A_B_C_D       |B       |
|A_B_C_D       |C       |
|A_B_C_D       |D       |

I know that I should use PIVOT. But what aggragate function do I use and are the arguments for the FOR statement right

SELECT DP.Name, value  
FROM RitopDatenpunkt DP  
    CROSS APPLY STRING_SPLIT(DP.Name, '_')
PIVOT
(
        GROUPING(Name) as Name
        FOR value in ([first],[second],[third],[fourth])
)piv;

score 3 · Accepted Answer · answered Oct 25 '20 at 13:46

Here is one way to do it using JSON functions:

select t.name,
    json_value(x.obj, '$[0]') name1,
    json_value(x.obj, '$[1]') name2,
    json_value(x.obj, '$[2]') name2,
    json_value(x.obj, '$[3]') name4
from mytable t
cross apply (values('["' + replace(t.name, '_', '", "') + '"]')) x(obj)

The trick is to manipulate the string to make it look like a JSON array (that' what the cross apply subquery does). Basically this turns a string like 'A_B_C' to '["A", "B", "C"]'. We can then use json_value() to easily access each individual element.

This does not assume anything about the elements being unique. Actually the only requirement is that the string should not contain embedded double quotes.

Demo on DB Fiddle:

name           | name1 | name2 | name2 | name4
:------------- | :---- | :---- | :---- | :----
ABC_DEFG_HIJKL | ABC   | DEFG  | HIJKL | null 
A_B_C          | A     | B     | C     | null 
A_B_C_D        | A     | B     | C     | D

score 1 · Answer 2 · answered Oct 25 '20 at 13:30

Assuming you have no duplicates in the string, you can use this rather cumbersome method:

SELECT dp.*, s.*
FROM RitopDatenpunkt DP CROSS APPY 
     (SELECT MAX(CASE WHEN SEQNUM = 1 THEN s.value END) as first,
             MAX(CASE WHEN SEQNUM = 2 THEN s.value END) as second,
             MAX(CASE WHEN SEQNUM = 3 THEN s.value END) as third,
             MAX(CASE WHEN SEQNUM = 4 THEN s.value END) as fourth
      FROM (SELECT s.*,
                   ROW_NUMBER() OVER (ORDER BY CHARINDEX('_' + s.value + '_', '_' + DP.Name + '_')) as seqnum
            FROM STRING_SPLIT(DP.Name, '_') s
           ) s
     ) s;

This uses CHARINDEX() to find the values in the original string and then uses conditional aggregation to create the columns in order.

Unfortunately, STRING_SPLIT() does not guarantee the ordering. An alternative approach is to use a recursive CTE or to mis-use the PARSENAME() function, if you don't have more than four components.

score 0 · Answer 3 · answered Oct 25 '20 at 16:54

You could use PARSENAME() too as:

SELECT Val,
       PARSENAME(Value, 1) Name1,
       PARSENAME(Value, 2) Name2,
       PARSENAME(Value, 3) Name3,
       PARSENAME(Value, 4) Name4
FROM
(
  VALUES
  ('ABC_DEFG_HIJKL'), 
  ('A_B_C'),
  ('A_B_C_D')
) T(Val) CROSS APPLY (VALUES(REPLACE(Val, '_', '.'))) TT(Value);

Note that this won't work if you have more than 3 '_'.

SQL - Splitting string in multiple columns

3 Answers3