Create new column by each value separated by ";" SQL Server SUBSTRING

Question

I have this 'Tests' column with n Rows

1 Test0;Test1;Test2
2 Test3;Test5;Test8
...
...

I need to separate each value by semicolon and create 1 column for each index.

This should be the result:

Column New1: 
Test0
Test3
Column New2:
Test1
Test5
Column New3:
Test2
Test8

What is the SQL Server version and does `Tests` column always have text with 3 parts? Thanks. — Zhorov, Jun 16 '20 at 13:46

score 1 · Accepted Answer · answered Jun 16 '20 at 13:42

I think this is probably close enough to what you want:

select t.*, s.*
from t cross apply
     (select max(case when seqnum = 1 then s.value end) as test_1,
             max(case when seqnum = 2 then s.value end) as test_2,
             max(case when seqnum = 3 then s.value end) as test_3             
      from (select s.value,
                   row_number() over (order by charindex(';' + s.value + ';', ';' + t.tests + ';')) as seqnum
            from string_split(t.tests, ';') s
           ) s
     ) s;

Here is a db<>fiddle.

Note: This approach will not work if there are duplicates in the column.

Im having the trouble of duplicates in the column.. They are not properly order... — Daggle, Jun 30 '20 at 09:06

score 0 · Answer 2 · answered Jun 16 '20 at 13:44

Solved here How to split one column into two columns in SQL Server

(Note: this approach is made to split one column into two columns)

declare @t table (id int, name  varchar(50))

select 
    case when CHARINDEX(';', Column)>0 
         then SUBSTRING(Column, 1, CHARINDEX(';', Column)-1) 
         else Column end Column1, 
    CASE WHEN CHARINDEX(';', Column)>0 
         THEN SUBSTRING(Column, CHARINDEX(';',Column)+1,len(Column))  
         ELSE NULL END as Column2
from @t

score 0 · Answer 3 · answered Jun 16 '20 at 14:18

Try this

select SUBSTRING(Tests,1,CHARINDEX(';', Tests)-1) as New1,
SUBSTRING(Tests,CHARINDEX(';', Tests)+1,CHARINDEX(';', Tests,CHARINDEX(';', Tests)-1)-1) as New2,
SUBSTRING(Tests,CHARINDEX(';', Tests,CHARINDEX(';', Tests)+1)+1,len(Tests)) as New3

Create new column by each value separated by ";" SQL Server SUBSTRING

3 Answers3