CODEWITHSUNDEEP
sql-server
drummer boi
drummer boi

Reputation: 23

Splitting values from one column into multiple columns in SQL with different delimiters

How to do this in SQL in any way possible?

Please see image or below:

||    Id    ||    Column1                      ||
||  1000    ||  SA(13), DS(1)                  ||
||  2000    ||  QW(1)                          ||
||  3000    ||  TE(23), RE(1), BB(40), VV(5)   ||

Result should be:

|| Id   ||  Column2    ||  Colum3  ||
|| 1000 ||    SA       ||    13    ||
|| 1000 ||    DS       ||     1    ||
|| 2000 ||    QW       ||     1    ||
|| 3000 ||    TE       ||    23    ||
|| 3000 ||    RE       ||     1    ||
|| 3000 ||    BB       ||    40    ||
|| 3000 ||    VV       ||     5    ||

screenshot of the sample table

Upvotes: 1

Views: 1334

Answers (2)

SqlZim
SqlZim

Reputation: 38023

Using a CSV Splitter function by Jeff Moden along with left() and substring():

select 
    Id
 , col2 = left(x.Item,charindex('(',x.Item)-1)
 , col3 = substring(x.Item
          ,charindex('(',x.Item)+1
          ,charindex(')',x.Item)-charindex('(',x.Item)-1
          )
from t
  cross apply (
    select Item = ltrim(rtrim(i.Item))
      from [dbo].[delimitedsplit8K](t.col,',') as i
      ) x

returns:

test setup: http://rextester.com/IOKB65736

+------+------+------+
|  Id  | col2 | col3 |
+------+------+------+
| 1000 | SA   |   13 |
| 1000 | DS   |    1 |
| 2000 | QW   |    1 |
| 3000 | TE   |   23 |
| 3000 | RE   |    1 |
| 3000 | BB   |   40 |
| 3000 | VV   |    5 |
+------+------+------+

splitting strings reference:

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269483

One way to do this in SQL Server is a recursive CTE:

with cte as (
      select id,
             left(column1, charindex(',', column1) - 1) as col23,
             substring(column1, charindex(',', column1) + 1) + ',' as rest
      from t
      union all
      select id,
             left(rest, charindex(',', rest) - 1) as col23
             substring(rest, charindex(',', rest) + 1) as rest
      from t
      where rest like '%,%'
     )
select id, left(col23, 2) as column2,
       replace(replace(substring(col23, 3, len(col23)), '(', ''), ')', '') as column3
from cte;

Note: This assumes that column2 has two characters (as in your example data). If this can vary, you can also use charindex() to get split col23.

Upvotes: 1

Related Questions