CODEWITHSUNDEEP
sql-servert-sql
LordRofticus
LordRofticus

Reputation: 237

SQL Split String Into Columns

I have field that contains financial account codes:

1000000-MER-MHO-GEN--------
1000000-MER-MHO-GEN-MUS-------
1000000-MER-MHO-GEN----RUG----
1000000-MER-MHO-GEN--------VET

I would like to split it into columns Seg01 to Seg12 with - as the delimiter

The first 4 segments will always be there, then there after one or more segments could possibly exist.

Upvotes: 1

Views: 504

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

Here's a quick option using a bit of JSON.

Example

Declare @YourTable Table ([SomeCol] varchar(50))  
Insert Into @YourTable Values 
 ('1000000-MER-MHO-GEN--------')
,('1000000-MER-MHO-GEN-MUS-------')
,('1000000-MER-MHO-GEN----RUG----')
,('1000000-MER-MHO-GEN--------VET')
 
Select A.* 
      ,Pos1  = JSON_VALUE(S,'$[0]')
      ,Pos2  = JSON_VALUE(S,'$[1]')
      ,Pos3  = JSON_VALUE(S,'$[2]')
      ,Pos4  = JSON_VALUE(S,'$[3]')
      ,Pos5  = JSON_VALUE(S,'$[4]')
      ,Pos6  = JSON_VALUE(S,'$[5]')
      ,Pos7  = JSON_VALUE(S,'$[6]')
      ,Pos8  = JSON_VALUE(S,'$[7]')
      ,Pos9  = JSON_VALUE(S,'$[8]')
      ,Pos10 = JSON_VALUE(S,'$[9]')
      ,Pos11 = JSON_VALUE(S,'$[10]')
      ,Pos12 = JSON_VALUE(S,'$[11]')
From @YourTable A
Cross Apply ( values ( '["'+replace(SomeCol,'-','","')+'"]' ) ) B(S)

Results

enter image description here

Just in case <2016 ... this is an XML Approach

Select A.*
      ,Pos1 = xDim.value('/x[1]' ,'varchar(100)')  -- Select the appropriate data type
      ,Pos2 = xDim.value('/x[2]' ,'varchar(100)')
      ,Pos3 = xDim.value('/x[3]' ,'varchar(100)')
      ,Pos4 = xDim.value('/x[4]' ,'varchar(100)')
      ,Pos5 = xDim.value('/x[5]' ,'varchar(100)')
      ,Pos6 = xDim.value('/x[6]' ,'varchar(100)')
      ,Pos7 = xDim.value('/x[7]' ,'varchar(100)')
      ,Pos8 = xDim.value('/x[8]' ,'varchar(100)')
      ,Pos9 = xDim.value('/x[9]' ,'varchar(100)')
      ,Pos10= xDim.value('/x[10]','varchar(100)')
      ,Pos11= xDim.value('/x[11]','varchar(100)')
      ,Pos12= xDim.value('/x[12]','varchar(100)')
From @YourTable A
Cross Apply ( values (convert(xml,'<x>' + replace(A.SomeCol,'-','</x><x>')+'</x>')) )B(xDim)

Upvotes: 6

Related Questions