How to split string in T-SQL by columns?

Question

I know that this question had been asked a lot by I can not find suitable solution for my case.

I have variations of a string:

valA|valB|1|val1a|Val1b|Val2a|Val2b|Val3a|Val3b

Where 3rd element is between 0 and 3 and shows how many ValNa and ValNb will be after it. When it is 0 then no elements after it. If 1 then only 1 couple of elements will be after it (Val1a and Val1b).

I need TSQL select that return each element as column. For example:

SELECT valA, valB, 1, val1a, Val1b

I can not use PARSENAME because it allows 4 elements max.

Here are examples of all my combinations:

valA|valB|0

valA|valB|1|val1a|Val1b

valA|valB|2|val1a|Val1b|Val2a|Val2b

valA|valB|3|val1a|Val1b|Val2a|Val2b|Val3a|Val3b

Rigth now I am using this solution but I can not manage final element:

DECLARE @param AS VARCHAR(MAX) = 'valA|ValB|2|val1a|val1b|val2a|val2b';
DECLARE @delimiter AS CHAR(1) = '|';

SELECT a FROM (VALUES (@param + @delimiter)) AS MyTable(a);

SELECT 
    CASE WHEN P1.Pos>0 THEN LEFT(Prm,P1.Pos-1) ELSE '' END AS valA, 
    CASE WHEN P2.Pos>0 THEN SUBSTRING (Prm, P1.Pos + 1, P2.Pos - P1.Pos - 1) ELSE '' END AS valB, 
    CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END AS Num, 
    CASE WHEN (P4.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END >= 1) THEN SUBSTRING (Prm, P3.Pos + 1, P4.Pos - P3.Pos - 1) ELSE '' END AS val1a, 
    CASE WHEN (P5.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END >= 1) THEN SUBSTRING (Prm, P4.Pos + 1, P5.Pos - P4.Pos - 1) ELSE '' END AS val1b, 
    CASE WHEN (P6.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END >= 2) THEN SUBSTRING (Prm, P5.Pos + 1, P6.Pos - P5.Pos - 1) ELSE '' END AS val2a, 
    CASE WHEN (P7.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END >= 2) THEN SUBSTRING (Prm, P6.Pos + 1, P7.Pos - P6.Pos - 1) ELSE '' END AS val2b, 
    CASE WHEN (P8.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END = 3) THEN SUBSTRING (Prm, P7.Pos + 1, P8.Pos - P7.Pos - 1) ELSE '' END AS val3a, 
    CASE WHEN (P9.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END = 3) THEN SUBSTRING (Prm, P8.Pos + 1, P9.Pos - P8.Pos - 1) ELSE '' END AS val3b 
FROM 
    (VALUES (@param + @delimiter)) AS baseTable(Prm) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param))) as P1(Pos) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P1.Pos+1))) as P2(Pos) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P2.Pos+1))) as P3(Pos) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P3.Pos+1))) as P4(Pos) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P4.Pos+1))) as P5(Pos) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P5.Pos+1))) as P6(Pos) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P6.Pos+1))) as P7(Pos) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P7.Pos+1))) as P8(Pos) 
    CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P8.Pos+1))) as P9(Pos) 

EDIT:

I need a kind of bullet-proof solution so even my string is empty the SQL just return empty values.

Please, who vote down for my question, can you just put comment why? So I can take a note and not make the same mystake again.

Answer 1

Test Data

DECLARE @t TABLE (Value VARCHAR(8000))

INSERT INTO @t VALUES 
('valA|valB|0'),
('valA|valB|1|val1a|Val1b'),
('valA|valB|2|val1a|Val1b|Val2a|Val2b'),
('valA|valB|3|val1a|Val1b|Val2a|Val2b|Val3a|Val3b')

Query

;WITH Split_Fields (Field, xmlfields)
AS
(
    SELECT Value AS Field,
    CONVERT(XML,'<Fields><field>'  
    + REPLACE(Value,'|', '</field><field>') + '</field></Fields>') AS xmlfields
      FROM @t
)

 SELECT Field      
 ,xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1    
 ,xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2
 ,xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3    
 ,xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4
 ,xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
 ,xmlfields.value('/Fields[1]/field[6]','varchar(100)') AS Field6
 ,xmlfields.value('/Fields[1]/field[7]','varchar(100)') AS Field7
 ,xmlfields.value('/Fields[1]/field[8]','varchar(100)') AS Field8
 ,xmlfields.value('/Fields[1]/field[9]','varchar(100)') AS Field9
 ,xmlfields.value('/Fields[1]/field[10]','varchar(100)') AS Field10
 FROM Split_Fields

Result

╔═════════════════════════════════════════════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦═════════╗
║                      Field                      ║ Field1 ║ Field2 ║ Field3 ║ Field4 ║ Field5 ║ Field6 ║ Field7 ║ Field8 ║ Field9 ║ Field10 ║
╠═════════════════════════════════════════════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬═════════╣
║ valA|valB|0                                     ║ valA   ║ valB   ║      0 ║ NULL   ║ NULL   ║ NULL   ║ NULL   ║ NULL   ║ NULL   ║ NULL    ║
║ valA|valB|1|val1a|Val1b                         ║ valA   ║ valB   ║      1 ║ val1a  ║ Val1b  ║ NULL   ║ NULL   ║ NULL   ║ NULL   ║ NULL    ║
║ valA|valB|2|val1a|Val1b|Val2a|Val2b             ║ valA   ║ valB   ║      2 ║ val1a  ║ Val1b  ║ Val2a  ║ Val2b  ║ NULL   ║ NULL   ║ NULL    ║
║ valA|valB|3|val1a|Val1b|Val2a|Val2b|Val3a|Val3b ║ valA   ║ valB   ║      3 ║ val1a  ║ Val1b  ║ Val2a  ║ Val2b  ║ Val3a  ║ Val3b  ║ NULL    ║
╚═════════════════════════════════════════════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩═════════╝