具有4个定界符的TSQL解析字符串

问题描述：

我正在尝试解析此字符串:

I am trying to parse this string :

'200 | 50 | jo.th@xxx.com | 09 \ 23 \ 2016 | 07:00:00'

'200|50|jo.th@xxx.com|09\23\2016|07:00:00'

分成5列，我感到沮丧.

into 5 columns and I am getting frustrated.

定界符是管道|

字段不是固定的，因此我需要使用charindex才能找到定界符的位置?

The fields are not fixed so I need to use charindex in order to find the location of the delimiter ?

请帮助谢谢

答

另一个选项如下.可以将其烘焙到TVF中，甚至进行交叉应用

Another option is as follows. This can be baked into a TVF or even a Cross Apply

Declare @String varchar(max) = '200|50|jo.th@xxx.com|09\23\2016|07:00:00'

Select Pos1 = xDim.value('/x[1]','varchar(max)')
      ,Pos2 = xDim.value('/x[2]','varchar(max)')
      ,Pos3 = xDim.value('/x[3]','varchar(max)')
      ,Pos4 = xDim.value('/x[4]','varchar(max)')
      ,Pos5 = xDim.value('/x[5]','varchar(max)')
      ,Pos6 = xDim.value('/x[6]','varchar(max)')
      ,Pos7 = xDim.value('/x[7]','varchar(max)')
      ,Pos8 = xDim.value('/x[8]','varchar(max)')
      ,Pos9 = xDim.value('/x[9]','varchar(max)')
 From (Select Cast('<x>' + Replace(@String,'|','</x><x>')+'</x>' as XML) as xDim) A

编辑-交叉使用-易于扩展/收缩

Declare @YourTable table (ID int,SomeString varchar(max))
Insert Into @YourTable values
(1,'200|50|jo.th@xxx.com|09\23\2016|07:00:00'),
(2,'400|99|james.th@xxx.com|11\15\2016|09:00:00')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = xDim.value('/x[1]','varchar(max)')
                      ,Pos2 = xDim.value('/x[2]','varchar(max)')
                      ,Pos3 = xDim.value('/x[3]','varchar(max)')
                      ,Pos4 = xDim.value('/x[4]','varchar(max)')
                      ,Pos5 = xDim.value('/x[5]','varchar(max)')
                      ,Pos6 = xDim.value('/x[6]','varchar(max)')
                      ,Pos7 = xDim.value('/x[7]','varchar(max)')
                      ,Pos8 = xDim.value('/x[8]','varchar(max)')
                      ,Pos9 = xDim.value('/x[9]','varchar(max)')
                 From (Select Cast('<x>' + Replace(A.SomeString,'|','</x><x>')+'</x>' as XML) as xDim) A
             ) B

具有4个定界符的TSQL解析字符串

相关推荐