I have table1 with columnA and data like this
columnA
U001.C18754
I want to break down with 2 new column clumnB and columnC by (.)period between data
columnA columnB columnC
U001.C18754 U001 C18754
My code
Select
a.columnA
,b.columnB
,b.columnC
From table1 a
Cross Apply (
Select columnA = xDim.value('/x[2]','varchar(50)')
,columnB = xDim.value('/x[3]','varchar(50)')
,columnC = xDim.value('/x[4]','varchar(50)')
From ( values (cast('<x>' replace(varstring,'.','</x><x>') '</x>' as xml))) A(xDim)
) b
I got error Invalid column name 'varString'
Not sure how to correct this error. Thank you.
CodePudding user response:
If less than 4 segments, you may want to consider parsename()
Example
Declare @YourTable Table ([columnA] varchar(50)) Insert Into @YourTable Values
('U001.C18754')
Select *
,columnB = parsename(columnA,2)
,columnC = parsename(columnA,1)
from @YourTable
Results
columnA columnB columnC
U001.C18754 U001 C18754
EDIT - Update for XML
Select a.columnA
,b.columnB
,b.columnC
From @YourTable a
Cross Apply (
Select columnB = xDim.value('/x[1]','varchar(50)')
,columnC = xDim.value('/x[2]','varchar(50)')
From ( values (cast('<x>' replace(columnA,'.','</x><x>') '</x>' as xml))) as A(xDim)
) b