Using string_split to store data in multiple columns instead of just one?

Question

I am trying to save a data of string2 ='DOB;Mar 1199;passport;AW1234567' into multiple columns of the table but it only move it to 1st column. I am using string_split function to separate all 4 string parts separated by ";". What should I do to move this data into a single row across 4 columns? Please see the details and result image below:

use TEST
DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'
DECLARE @myTableVariable TABLE (id INT IDENTITY(1,1) PRIMARY KEY, name varchar(max))

insert into @myTableVariable
(name)
SELECT value FROM STRING_SPLIT(@string2, @sep);

print @string2;


insert into EMPLOYEE (dob1)
select name from @myTableVariable 

Answer 1

To guarantee column ordering you can't rely on string_split so need a different user defined function. This one returns the same value column and also a seq column for row ordering:

create function dbo.SplitString(@string varchar(max), @Delimiter varchar(1))
returns table
as
   return(  
    select j.[value], 1 + Convert(tinyint,j.[key]) Seq 
      from OpenJson(Concat('["',replace(@string, @delimiter , '","'),'"]')) j
   );

You can then make use of it as follows to create the columns from the sample string and insert into the target table:

declare @string2 varchar(max)='DOB;Mar 1199;passport;AW1234567', @sep char(1)=';'

insert into Employee(Dob1, DobNum, Pass1, PassNum)
select 
    Max(case when Seq=1 then Value end) Dob1,
    Max(case when Seq=2 then Value end) DobNum,
    Max(case when Seq=3 then Value end) Pass1,
    Max(case when Seq=4 then Value end) PassNum
from dbo.SplitString(@string2, @sep);

Example Fiddle

Answer 2

SQL Server built-in PARSENAME() function could be handy for the task.

SQL

DECLARE @string2 varchar(max) = 'DOB;Mar 1199;passport;AW1234567';
DECLARE @sep char(1) = ';'
    , @dot CHAR(1) = '.';
DECLARE @employee TABLE (
    id INT IDENTITY(1,1) PRIMARY KEY, 
    Dob1 varchar(20), 
    DobNum VARCHAR(20),
    Pass1 VARCHAR(20),
    PassNum VARCHAR(20)
);

WITH rs AS
(
    SELECT REPLACE(@string2, @sep, @dot) AS tokenList
)
INSERT INTO @employee (Dob1, DobNum, Pass1, PassNum)
SELECT PARSENAME(tokenList,4)
    , PARSENAME(tokenList,3)
    , PARSENAME(tokenList,2)
    , PARSENAME(tokenList,1)
FROM rs;

-- test
SELECT * FROM @employee;

Output

+----+------+----------+----------+-----------+
| id | Dob1 |  DobNum  |  Pass1   |  PassNum  |
+----+------+----------+----------+-----------+
|  1 | DOB  | Mar 1199 | passport | AW1234567 |
+----+------+----------+----------+-----------+

Answer 3

It is possible to do this a number of ways, it is generally frowned upon, but because you have specifically requested STRING_SPLIT then I offer you this mechanism to parse the components of the string:

DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'

SELECT ROW_NUMBER() OVER (order by (SELECT 1)) as RN, Value
FROM STRING_SPLIT(@string2, @sep, 1)

RN	Value
1	DOB
2	Mar 1199
3	passport
4	AW1234567

NOTE: This hack for the ORDER BY is generally advised against

From the docs: The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string

However you could use this are your own risk if you really needed to use STRING_SPLIT

You could now access these tokens via their ordinal:

DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'

;WITH Split as (
    SELECT ROW_NUMBER() OVER (order by (select 1)) as RN, Value
    FROM STRING_SPLIT(@string2, @sep)
)
SELECT s1.value as DOBKey, s2.value as DOBValue, s3.value as PassportKey, s4.value as PassportValue 
FROM Split as s1, Split as s2, Split as s3, Split as s4
WHERE s1.RN = 1 
  AND s2.RN = 2
  AND s3.RN = 3
  AND s4.RN = 4

Results in this:

DOBKey	DOBValue	PassportKey	PassportValue
DOB	Mar 1199	passport	AW1234567

SQL Azure Supports an enable_ordinal flag

As an update to SQL 2019 and expected to be released soon, there is an enable_ordinal argument that can be used to ensure the sequence of the string tokens and it will include the ordinal value in an output column named ordinal.

HOWEVER as of 11/06/2021 enable_ordinal argument and ordinal output column are currently only supported in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (serverless SQL pool only).

When available we can simplify the previous query:

DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'

;WITH Split as (
    SELECT Ordinal, Value
    FROM STRING_SPLIT(@string2, @sep, 1)
)
SELECT s1.Value as DOBKey, s2.Value as DOBValue, s3.Value as PassportKey, s4.Value as PassportValue 
FROM Split as s1, Split as s2, Split as s3, Split as s4
WHERE s1.Ordinal = 1 
  AND s2.Ordinal = 2
  AND s3.Ordinal = 3
  AND s4.Ordinal = 4

You can use the same CTE to extract the DOBValue and the PassportValue in the INSERT:

DECLARE @string2 varchar(max);
DECLARE @sep char(1);
set @string2 = 'DOB;Mar 1199;passport;AW1234567';
set @sep = ';'

;WITH Split as (
    SELECT Ordinal, Value
    FROM STRING_SPLIT(@string2, @sep, 1)
)
insert into EMPLOYEE (dob1, pass1)
select s2.Value, s4.Value 
FROM Split as s1, Split as s2, Split as s3, Split as s4
WHERE s1.Ordinal = 1 
  AND s2.Ordinal = 2
  AND s3.Ordinal = 3
  AND s4.Ordinal = 4

Should result in this:

ID	dob1	pass1
1	Mar 1199	AW1234567