Need help splitting a ^-delimited column into several columns

Transact-SQL
1

Let's say I have a table named Sample with the following columns:

ID - int identity(1,1)
Codes - varchar(50)
Code1 - varchar(10)
Code2 - varchar(10)
Code3 - varchar(10)

Let's say the table has the following data:

ID, Codes, Code1, Code2, Code3
1, A01^A02^A03, null, null, null
2, B01^B02^B03, null, null, null
3, C01^C02, null, null, null
4, D01, null, null, null

Notice that the Codes column is a list of values delimited by a caret (^). Assuming that there are a maximum of three values in the Codes column, I would like to update the Code1, Code2, and Code3 columns with these values so that the data in the table becomes:

ID, Codes, Code1, Code2, Code3
1, A01^A02^A03, A01, A02, A03
2, B01^B02^B03, B01, B02, B03
3, C01^C02, C01, C02, null
4, D01, D01, null, null

How do I do this? Please show your transact SQL code solution.

Thanks in advance!

2

What have you tried so far - and where are you having problems?

1 Like
3

I have not tried anything yet. I have no idea how to solve it. :blush:

4

Look at your previous post about deptcode comma delimited

5

hi

delimited 8K Splitter .. function by Jeff Moden ..

6

Not necessary for this - much easier to cross apply to charindex for each position and substring.

Declare @sampleTable Table (
        id int Identity(1,1) Primary Key Clustered
      , Codes varchar(50)
      , Code1 varchar(10)
      , Code2 varchar(10)
      , Code3 varchar(10)
        );

 Insert Into @sampleTable (Codes, Code1, Code2, Code3)
 Values ('A01^A02^A03', Null, Null, Null)
      , ('B01^B02^B03', Null, Null, Null)
      , ('C01^C02', Null, Null, Null)
      , ('D01', Null, Null, Null);

 Select *
   From @sampleTable st;

 Select *
      , Code1 = substring(v.Codes, 1, p1.pos - 1)
      , Code2 = substring(v.Codes, p1.pos + 1, p2.pos - p1.pos - 1)
      , Code3 = substring(v.Codes, p2.pos + 1, p3.pos - p2.pos - 1)
   From @sampleTable                                            st
  Cross Apply (Values (concat(st.Codes, '^^^')))                 v(Codes)
  Cross Apply (Values (charindex('^', v.Codes, 1)))             p1(pos)
  Cross Apply (Values (charindex('^', v.Codes, p1.pos + 1)))    p2(pos)
  Cross Apply (Values (charindex('^', v.Codes, p2.pos + 1)))    p3(pos);

 Update @sampleTable
    Set Code1 = substring(v.Codes, 1, p1.pos - 1)
      , Code2 = substring(v.Codes, p1.pos + 1, p2.pos - p1.pos - 1)
      , Code3 = substring(v.Codes, p2.pos + 1, p3.pos - p2.pos - 1)
   From @sampleTable                                            st
  Cross Apply (Values (concat(st.Codes, '^^^')))                 v(Codes)
  Cross Apply (Values (charindex('^', v.Codes, 1)))             p1(pos)
  Cross Apply (Values (charindex('^', v.Codes, p1.pos + 1)))    p2(pos)
  Cross Apply (Values (charindex('^', v.Codes, p2.pos + 1)))    p3(pos);

 Select *
   From @sampleTable st;

Using a splitter - you have to split the string into rows, get the values from each row and the group the results.