Varstring issue

user_18507604 0
Viewed 31

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.

sql-server sql
1 Answers

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
user_15700000
Related