我在MSsql中有这么长的字符串,我需要I值.
我代表发票,
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)
