Search code examples
sql-serverparsingjoindelimiter

Parse Columns in SQL by delimiter


In SQL Server, I have a table/view that has multiple columns. The last column looks like this:

COL
---------------------------------
|test|test|test11|testing|final
|test|test|test1|testing2|final3
|test|test|test17|testing|final6

How do parse this column by | and combine it with the right side of the existing table like such:

COL1     COL2                                COL     Parse1    Parse2    Parse3   Parse4    Parse5
   1        4    |test|test|test11|testing|final       test      test    test11  testing     final
   2        6   |test|test|test1|testing2|final3       test      test    test1  testing2    final3
   5        9   |test|test|test17|testing|final6       test      test    test17  testing    final6

There are the same number of parsings for column COL.

Any help would be great thanks!


Solution

  • Not clear if you have a leading | in the field COL. If so, you may want to shift /x[n]

    The pattern is pretty clear. Easy to expand or contract as necessary

    Example

    Declare @YourTable Table ([COL] varchar(50))
    Insert Into @YourTable Values 
     ('test|test|test11|testing|final')
    ,('test|test|test1|testing2|final3')
    ,('test|test|test17|testing|final6')
    
    Select A.* 
          ,B.*
     From  @YourTable A
     Cross Apply (
                    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                          ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                          ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                          ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                    From  (Select Cast('<x>' + replace((Select replace(A.Col,'|','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as B1
                 ) B
    

    Returns

    enter image description here