Akhil Akhil - 8 days ago 5
SQL Question

SQL query to get the multiple "," positions from a string

I have 5 rows of data like as below

Image 1

Now I need to find the position of every ',' from my input string.

My output should be like this:

Image 2

Answer

Using a split/parse within a Cross Apply (no udf).

Expand the top conditional aggregation as need (i.e. nth Position)

Not clear if you need to go dynamic.

Declare @YourTable table (ID varchar(max))
Insert Into @YourTable values
('32132'),
('32132,31132'),
('21313,32156,31656,32132,')

Select ID
      ,Length = max(Length)
      ,[1st Position] = max(case when RetSeq=1 and RN>1 then Pos else 0 end)
      ,[2st Position] = max(case when RetSeq=2 and RN>1 then Pos else 0 end)
      ,[3rd Position] = max(case when RetSeq=3 and RN>1 then Pos else 0 end)
      ,[4th Position] = max(case when RetSeq=4 and RN>1 then Pos else 0 end)
      ,[5th Position] = max(case when RetSeq=5 and RN>1 then Pos else 0 end)
From  (
        Select A.ID
              ,Length = len(A.ID)
              ,B.*
              ,Pos = (sum(Len(RetVal)) over (Partition By A.ID Order by B.RetSeq) + B.RetSeq) * sign(CharIndex(',',A.ID))
              ,RN  = Row_Number() over (Partition By A.ID Order By RetSeq Desc)
         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(A.ID,',','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                     ) B Where RetVal<>''

      ) A
 Group By ID

Returns

enter image description here

EDIT: To get the actual values

Declare @YourTable table (ID varchar(max))
Insert Into @YourTable values
('32132'),
('32132,31132'),
('21313,32156,31656,32132,')

Select A.ID
      ,Length = len(A.ID)
      ,B.*
 From @YourTable A
         Cross Apply (
            Select Pos1 = Isnull(xDim.value('/x[1]','varchar(max)'),'')
                  ,Pos2 = Isnull(xDim.value('/x[2]','varchar(max)'),'')
                  ,Pos3 = Isnull(xDim.value('/x[3]','varchar(max)'),'')
                  ,Pos4 = Isnull(xDim.value('/x[4]','varchar(max)'),'')
                  ,Pos5 = Isnull(xDim.value('/x[5]','varchar(max)'),'')
                  ,Pos6 = Isnull(xDim.value('/x[6]','varchar(max)'),'')
                  ,Pos7 = Isnull(xDim.value('/x[7]','varchar(max)'),'')
                  ,Pos8 = Isnull(xDim.value('/x[8]','varchar(max)'),'')
                  ,Pos9 = Isnull(xDim.value('/x[9]','varchar(max)'),'')
             From (Select Cast('<x>' + Replace(A.ID,',','</x><x>')+'</x>' as XML) as xDim) A
      ) B
Comments