How to separate string with delimiter into multiple columns?

Question

I want to separate the single col data into multiple columns. I have a column named LocationCode which is of varying length in a table.

Sample Data :

LocationCode (col name)

100.23432.356345.6765634.34324.5645.F
100.2343.565465.56756765756756.4535435345.76466.F
200.234324234.46565466456.678678678678.543545445.43243243.F
1502.23.5.56546.7657767575.567567.MGR

Note : Separate all these into different column. Expected Output :

Column1   Column2   Column3     Column4      Column5    Column6    Column7
100       23432     356345      6765634       34324      5645         F
100       2343      565465   56756765756756  4535435345  76466        F
200    234324234  46565466456  678678678678  543545445  43243243      F
1502      23           5          56546     7657767575   567567      MGR

My sample data is separated by delimiter. and length of integer value differs. The newly formed columns should be separated by a delimiter.

"Split" is the keyword to use in your Google/Stackoverflow search. — shawnt00
– shawnt00, Commented Sep 8, 2019 at 19:04
This may help , pls check stackoverflow.com/questions/5096630/… — Arya
– Arya, Commented Sep 8, 2019 at 19:16

Ilyes · Accepted Answer · 2019-09-08 19:35:56Z

2

You're looking for PIVOT

SELECT [1] Col1,
       [2] Col2,
       [3] Col3,
       [4] Col4,
       [5] Col5,
       [6] Col6,
       [7] Col7
FROM
(
  VALUES
  ('100.23432.356345.6765634.34324.5645.F'),
  ('100.2343.565465.56756765756756.4535435345.76466.F'),
  ('200.234324234.46565466456.678678678678.543545445.43243243.F'),
  ('1502.23.5.56546.7657767575.567567.MGR')
) T(V)
CROSS APPLY
(
  SELECT Value VV,
         ROW_NUMBER() OVER(ORDER BY(SELECT 1)) RN
  FROM STRING_SPLIT(T.V, '.') --Use your own function since you have 2012 version
) TT(VV, RN)
PIVOT
(
  MAX(VV) FOR RN IN([1], [2], [3], [4], [5], [6], [7])
) TVP;

This will generate the 7 columns (only 7) for you, and if you have more or less then you could use a dynamic PIVOT.

Online Demo

answered Sep 8, 2019 at 19:35

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Cappelletti Over a year ago

First thought as well, but OP tagged 2012

Ilyes Over a year ago

Yeah, you also provide a good example there @JohnCappelletti ;)

HABO Over a year ago

From String_Split: "The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string." I'd be surprised if ROW_NUMBER() OVER(ORDER BY(SELECT 1)) will always generate the corresponding unpredictable order to match the substrings.

Rajat · Accepted Answer · 2019-09-08 21:46:09Z

0

The XML approach is probably cleaner, but here is a divide and conquer approach

with cte1 as 
(select col,charindex('.',col,charindex('.',col,(charindex('.',col)+1))+1) ind from table)

,cte2 as
(SELECT col, right(col,len(col)-ind) p1, left(col,ind-1) p2 from cte1)

select 
col,
parsename(p2,3) col1,
parsename(p2,2) col2,
parsename(p2,1) col3,
parsename(p1,4) col4,
parsename(p1,3) col5,
parsename(p1,2) col6,
parsename(p1,1) col7
from cte2;

Demo Fiddle

edited Sep 8, 2019 at 21:46

answered Sep 8, 2019 at 21:40

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Comments

Neeraj Agarwal · Accepted Answer · 2019-09-09 00:38:08Z

Another approach to the solution using cte and conditional aggregation:

with cte1(LocationCode, code, seqnum) as
(
select LocationCode, value as code,
    row_number() over(partition by LocationCode order by (select null)) as seqnum
from location
cross apply string_split(LocationCode, '.')
)
select LocationCode,
    max(case when seqnum = 1 then rtrim(code) end) as Column1,
    max(case when seqnum = 2 then rtrim(code) end) as Column2,
    max(case when seqnum = 3 then rtrim(code) end) as Column3,
    max(case when seqnum = 4 then rtrim(code) end) as Column4,
    max(case when seqnum = 5 then rtrim(code) end) as Column5,
    max(case when seqnum = 6 then rtrim(code) end) as Column6,
    max(case when seqnum = 7 then rtrim(code) end) as Column7
from cte1
group by LocationCode

Collectives™ on Stack Overflow

How to separate string with delimiter into multiple columns?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related