Parsing a column into multiple columns in SQL Serv

问题:

I'm trying to get this row to go from this:

Col1        Col2            Label   String                              
--------------------------------------------------------------------
6974449644  632999378491781 APDP    100.65USD/57.77USD/0167025349549    

to this:

Col1        Col2            Label   old     curr   new    curr2   number               
--------------------------------------------------------------------------
6974449644  632999378491781 APDP    100.65  USD    57.77  USD     0167025349549 

回答1:

One option is a little XML in concert with a CROSS APPLY

Note this assumes the Currancy is 3 characters

Example

Select A.Col1
      ,A.Col2
      ,A.Label
      ,Old   = convert(money,left(Pos1,len(Pos1)-3))
      ,Curr  = right(Pos1,3)
      ,New   = convert(money,left(Pos2,len(Pos2)-3))
      ,Curr2 = right(Pos2,3)
      ,Number= Pos3
 From  YourTable A
 Cross Apply (
                 Select Pos1 = n.value('/x[1]','varchar(max)')
                       ,Pos2 = n.value('/x[2]','varchar(max)')
                       ,Pos3 = n.value('/x[3]','varchar(max)')
                  From  (Select cast('<x>' + replace(A.String,'/','</x><x>')+'</x>' as xml) as n) X
             ) B

Returns

Col1        Col2            Label   Old     Curr    New    Curr2    Number
6974449644  632999378491781 APDP    100.65  USD     57.77  USD  0167025349549

Edit to See questionable data

Select A.*
      ,B.*
 From  YourTable A
 Cross Apply (
                 Select Pos1 = n.value('/x[1]','varchar(max)')
                       ,Pos2 = n.value('/x[2]','varchar(max)')
                       ,Pos3 = n.value('/x[3]','varchar(max)')
                  From  (Select cast('<x>' + replace(A.String,'/','</x><x>')+'</x>' as xml) as n) X
             ) B