Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Remove unwanted character and get new columns

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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)')
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading