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.
config settingandvaluecolumns. My problem is that in that value I have this string. I need to expand it as columns. I speak about 1 string and from that point I don't want (can not) change my current logic. In general I agree about normalization, but in this case I am against it.