Remove unwanted character and get new columns-CodePudding

I have a table1

skillname  skillid
Test       1100246|3;1100247|3;1102797|3;1108399|3;

My goal to get rid of |3; and result like this

skillname  skillid                                    New1     New2       New3    New4  

Test      1100246|3;1100247|3;1102797|3;1108399|3;   1100246   1100247   1102797  1108399

My code

Select
      SkillName
      ,SkillID
      ,New1
      ,New2
      ,New3
      ,New4
  FROM Table1
 Cross Apply (
                Select New1 = xDim.value('/x[2]','varchar(50)') 
                      ,New2 = xDim.value('/x[3]','varchar(50)')
                      ,New3 = xDim.value('/x[4]','varchar(50)')
                      ,New4 = xDim.value('/x[5]','varchar(50)')
                From  ( values (cast('<x>'   replace(SkillID,'|3;','</x><x>') '</x>' as xml)))  A(xDim)
                        )b

The code does not work well. Anyone can see the issues. Thank you
And the result

SKillname SkillID                                  New1        New2 New3  New4
Test     1100246|3;1100247|3;1102797|3;1108399|3;  1133797|3  NULL  NULL  NULL

CodePudding user response：

Your indexes are off, using the following appears to work

Select 
   New1 = xDim.value('/x[1]','varchar(50)') 
  ,New2 = xDim.value('/x[2]','varchar(50)')
  ,New3 = xDim.value('/x[3]','varchar(50)')
  ,New4 = xDim.value('/x[4]','varchar(50)')