2

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.

2
  • 1
    Do yourself a favour and normalize your table. Never store multiple values in a single column Commented Aug 27, 2015 at 16:03
  • Those are configuration settings, @juergen d. I already have a table which I use and this value must fit in that table. It has config setting and value columns. 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. Commented Aug 27, 2015 at 16:05

1 Answer 1

4

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    ║
╚═════════════════════════════════════════════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩═════════╝
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for quick answer.
believe me or not just used this somewhere else about an hour ago lol :)
So I am very happy that you have to use that :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.