解析字符串SQL

我代表发票,
A代表金额,
D代表日期

I=940;A=29.5;D=20090901|I=941;A=62.54;D=20090910|I=942;A=58.99;D=20091005|I=954;A=93.45;D=20091201|I=944;A=96.76;D=20091101|I=946;A=52.5;D=20091101|I=943;A=28.32;D=20091101|I=945;A=52.5;D=20091101|I=955;A=79.81;D=20091201|I=950;A=25.2;D=20091124|I=948;A=31.86;D=20091110|I=949;A=28.32;D=20091120|I=947;A=25.2;D=20091109|I=951;A=242.54;D=20091124|I=952;A=28.32;D=20091129|I=956;A=38.94;D=20091210|I=957;A=107.39;D=20091215|I=958;A=32.55;D=20091228|I=959;A=27.3;D=20091228|I=960;A=24.79;D=20091230|I=1117;A=28.32;D=20100131|I=1115;A=272.58;D=20100131|I=1116;A=159.6;D=20100209

这是最可怕的案件之一.

这些都是发票编号,它们具有相关的值,我将用它们链接到另一个交易.我真的很感激,如果有人可以解释最好的方式来解决这个问题,如果可能的话,不用制作应用程序

解决方法

declare @s varchar(max) = 'I=940;A=29.5;D=20090901|I=941;A=62.54;D=20090910|I=942;A=58.99;D=20091005|I=954;A=93.45;D=20091201|I=944;A=96.76;D=20091101|I=946;A=52.5;D=20091101|I=943;A=28.32;D=20091101|I=945;A=52.5;D=20091101|I=955;A=79.81;D=20091201|I=950;A=25.2;D=20091124|I=948;A=31.86;D=20091110|I=949;A=28.32;D=20091120|I=947;A=25.2;D=20091109|I=951;A=242.54;D=20091124|I=952;A=28.32;D=20091129|I=956;A=38.94;D=20091210|I=957;A=107.39;D=20091215|I=958;A=32.55;D=20091228|I=959;A=27.3;D=20091228|I=960;A=24.79;D=20091230|I=1117;A=28.32;D=20100131|I=1115;A=272.58;D=20100131|I=1116;A=159.6;D=20100209'
declare @xml xml

select @xml = '<item><value>'+replace(replace(@s,';','</value><value>'),'|','</value></item><item><value>')+'</value></item>'

select N.value('substring(value[1],3)','int') as Invoice,N.value('substring(value[2],'money') as Amount,N.value('substring(value[3],'date') as [Date]
from @xml.nodes('item') as T(N)

结果：

Invoice     Amount                Date
----------- --------------------- ----------
940         29,50                 2009-09-01
941         62,54                 2009-09-10
942         58,99                 2009-10-05
954         93,45                 2009-12-01
944         96,76                 2009-11-01
946         52,50                 2009-11-01
943         28,32                 2009-11-01
945         52,50                 2009-11-01
955         79,81                 2009-12-01
950         25,20                 2009-11-24
948         31,86                 2009-11-10
949         28,32                 2009-11-20
947         25,20                 2009-11-09
951         242,54                2009-11-24
952         28,32                 2009-11-29
956         38,94                 2009-12-10
957         107,39                2009-12-15
958         32,55                 2009-12-28
959         27,30                 2009-12-28
960         24,79                 2009-12-30
1117        28,32                 2010-01-31
1115        272,58                2010-01-31
1116        159,60                2010-02-09

对于SQL Server 2005,您需要使用datetime而不是date

select N.value('substring(value[1],'datetime') as [Date]
from @xml.nodes('item') as T(N)

要从表中读取,您需要像这样做.

declare @s varchar(max) = 'I=940;A=29.5;D=20090901|I=941;A=62.54;D=20090910|I=942;A=58.99;D=20091005|I=954;A=93.45;D=20091201|I=944;A=96.76;D=20091101|I=946;A=52.5;D=20091101|I=943;A=28.32;D=20091101|I=945;A=52.5;D=20091101|I=955;A=79.81;D=20091201|I=950;A=25.2;D=20091124|I=948;A=31.86;D=20091110|I=949;A=28.32;D=20091120|I=947;A=25.2;D=20091109|I=951;A=242.54;D=20091124|I=952;A=28.32;D=20091129|I=956;A=38.94;D=20091210|I=957;A=107.39;D=20091215|I=958;A=32.55;D=20091228|I=959;A=27.3;D=20091228|I=960;A=24.79;D=20091230|I=1117;A=28.32;D=20100131|I=1115;A=272.58;D=20100131|I=1116;A=159.6;D=20100209'

declare @YourTable table(ID int,s varchar(max))
insert into @YourTable values
(1,@s),(2,@s)

select Y.ID,T.N.value('substring(value[1],T.N.value('substring(value[2],T.N.value('substring(value[3],'date') as [Date]
from @YourTable as Y
  cross apply (select cast('<item><value>'+replace(replace(Y.s,'</value></item><item><value>')+'</value></item>' as xml)) as X(XMLCol)
  cross apply X.XMLCol.nodes('item') as T(N)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!