解析字符串 SQL [英] Parsing a string SQL
问题描述
我在 MSSQL 中有这个很长的字符串，我需要 I 值.
I have this very long string in MSSQL which I require the I value.
I 代表发票，A 代表金额，D 代表日期
I represents Invoice, A represents Amount, D represents Date
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=20109|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=2009110=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=20091124|I=948;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=200919510;|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=20091217.3A=118;D=20100131|I=1115;A=272.58;D=20100131|I=1116;A=159.6;D=20100209
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
这是最可怕的案例之一.
This is one of the scariest cases.
每个都是发票编号，其中包含相关值，我将使用这些值链接到另一笔交易.如果可能的话，如果有人可以解释在不制作应用程序的情况下解决此问题的最佳方式，我将不胜感激
Each of these are invoice numbers which have relevant values which i will use to link to another transaction. I would really appreciate it if someone could explain the best manner to go about this without making a app if possible
推荐答案
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],3)', 'money') as Amount,
N.value('substring(value[3],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，您需要使用日期时间而不是日期
For SQL Server 2005 you need to use datetime instead of date
select N.value('substring(value[1],3)', 'int') as Invoice,
N.value('substring(value[2],3)', 'money') as Amount,
N.value('substring(value[3],3)', 'datetime') as [Date]
from @xml.nodes('item') as T(N)
要从表中读取数据，您需要这样做.
To read from a table you need to do it like this.
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],3)', 'int') as Invoice,
T.N.value('substring(value[2],3)', 'money') as Amount,
T.N.value('substring(value[3],3)', 'date') as [Date]
from @YourTable as Y
cross apply (select cast('<item><value>'+replace(replace(Y.s, ';','</value><value>'), '|','</value></item><item><value>')+'</value></item>' as xml)) as X(XMLCol)
cross apply X.XMLCol.nodes('item') as T(N)
这篇关于解析字符串 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
