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, Sep 08 '19 at 19:04
Take a peek at https://stackoverflow.com/questions/43789578/split-one-column-value-into-multiple-column-values/43789876#43789876 — John Cappelletti, Sep 08 '19 at 19:16
This may help , pls check https://stackoverflow.com/questions/5096630/how-to-split-string-using-delimiter-char-using-t-sql — Arya, Sep 08 '19 at 19:16

score 2 · Answer 1 · answered Sep 08 '19 at 19:35

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 08 '19 at 19:35

Ilyes

14,640
4
29
55

First thought as well, but OP tagged 2012 – John Cappelletti Sep 08 '19 at 19:47
1

Yeah, you also provide a good example there @JohnCappelletti ;) – Ilyes Sep 08 '19 at 19:49
1

From [`String_Split`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017): "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. – HABO Sep 09 '19 at 03:53

Radagast · Answer 2 · 2019-09-08T21:46:09.987

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

score 0 · Answer 3 · answered Sep 09 '19 at 00:38

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

How to separate string with delimiter into multiple columns?

3 Answers3