SQL Split String Into Columns

Question

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.

Please read this for some tips on improving your question. Any particular results you might expect from the strings you supplied? — HABO
– HABO, Commented Jun 16, 2021 at 20:53
While asking a question you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements. (2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL. (3) Desired output based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jun 16, 2021 at 21:10

John Cappelletti · Accepted Answer · 2021-06-16 22:45:54Z

6

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

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)

edited Jun 16, 2021 at 22:45

answered Jun 16, 2021 at 20:56

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

3 Comments

Zhorov Over a year ago

JSON-based approach is an appropriate solution, +1 from my side.

Yitzhak Khabinsky Over a year ago

Good answer, +1 from my side!

LordRofticus Over a year ago

Perfect. Just what I was looking for.

Collectives™ on Stack Overflow

SQL Split String Into Columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related