CODEWITHSUNDEEP
sqlsql-server
ngoc
ngoc

Reputation: 63

SQL break into 3 columns

I have a field name called 'stuff'
and some records like this 

info,caow~13909~182029~10593~H7Q0B7MM
info,caow~4964~152073~16863~MF4B8MBC
info,caow~590~265~517~H7Q0B7MM

My goal is to break into 3 columns like this 

columnA   ColumnB   ColumnC
13909     182029    10593
4964      152073    16863
590       265       517

Need your help please. Thank you.

Upvotes: 1

Views: 41

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Just another option using a little XML

Example

Select B.*
 From  YourTable A
 Cross Apply (
                Select Pos2 = xDim.value('/x[2]','varchar(max)')
                      ,Pos3 = xDim.value('/x[3]','varchar(max)')
                      ,Pos4 = xDim.value('/x[4]','varchar(max)')
                From  ( values (cast('<x>' + replace((Select replace(SomeCol,'~','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml)))  A(xDim)
             ) B

Returns

Pos2    Pos3    Pos4
13909   182029  10593
4964    152073  16863
590     265     517

EDIT - Slightly thinner version

Select B.*
 From  YourTable A
 Cross Apply (
                Select Pos2 = xDim.value('/x[2]','varchar(50)')
                      ,Pos3 = xDim.value('/x[3]','varchar(50)')
                      ,Pos4 = xDim.value('/x[4]','varchar(50)')
                From  ( values (cast('<x>' + replace(SomeCol,'~','</x><x>')+'</x>' as xml)))  A(xDim)
             ) B

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you don't care about the ordering, you can use:

select s.*
from t cross apply
     (select max(case when seqnum = 1 then value end) as value1,
             max(case when seqnum = 2 then value end) as value2,
             max(case when seqnum = 3 then value end) as value3
      from (select s.value,
                   row_number() over (order by (select null)) as seqnum
            from string_split(t.col, '~') s
            where s.value not like '%[^0-9]%'
           )
    ) s;

Actually, if you don't have duplicates, you can use:

row_number() over (order by (select charindex(s.value, t.col)) as seqnum

for the seqnum definition.

Upvotes: 1

Related Questions