Splitting Multiple Columns,Rows with Different Delimiters

Question

This is SQL Table(UserTable)

    ROWNO|                CODE                        |CODEID
    -----+--------------------------------------------+-------------+
    1     ABD~Monthly~1~2~3~4~5!INDIA~Weekly~1~2~2~3~1  19
    2     CED~Weekly~1~3~3~4~5!SA~Weekly~4~2~2~3~5      20

The Code Column Contains Complex Statement with Lots of delimiters

The Delimiter ~ denotes Column and ! denoted Row

I have to bring it back as a result like this

        CODE1| CODE2 |CODE3|CODE4|CODE5|CODE6|CODE7|CODEID
        -----+-------+-----+-----+-----+-----+-----+------+
        ABD   Monthly  1      2     3      4   5       19
        INDIA Weekly   1      2     2      3   1       19
        CED   Weekly   1      3     3      4   5       20
        SA    Weekly   4      2     2      3   5       20

Please Somebody help me here to get the query !

Thanks in advance , Jayendran

The table resides in userdb with in a SQL server – Jayendran Jun 11 '17 at 17:44 — Jayendran, Jun 11 '17 at 17:44

John Cappelletti · Accepted Answer · 2017-06-11T19:28:13.273

This is an Inline approach (no udfs)

Use OUTER APPLY to show NULL values

Example

Select C.*
      ,A.CODEID
 From YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(A.[CODE],'!','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) B
 Cross Apply (
                Select Code1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Code2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Code3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Code4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Code5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Code6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Code7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(B.RetVal,'~','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) C

Returns

dbFiddle

May i know what is the purpose of '§§Split§§' in the Code ? Also how can i use this symbol '§' in my Keyboard It seems totally different ! — Jayendran, Jun 12 '17 at 02:35
@JayendranRosh The §§Split§§ is used to ensure XML safe and truly uniquie parsing and to get this via the keyboard, while holding down the ALT key type 0167 and release — John Cappelletti, Jun 12 '17 at 10:42

score 0 · Answer 2 · answered Jun 11 '17 at 17:40

First you should really consider to change your datamodel, but that's on another paper :).

I don't know which database you are using, but you you can try the XML-way.

You can combine these answers:

For creating the rows https://stackoverflow.com/a/16083088/4132760

For createing the columns https://stackoverflow.com/a/15108499/4132760

If you are using SQl 2016 you can rely on the new split function https://stackoverflow.com/a/36305493/4132760

If you can provide your used databse system i'm sure somebody can give you a complete solution.

Splitting Multiple Columns,Rows with Different Delimiters

2 Answers2