Parsing a string SQL

Question

I have this very long string in MSSQL which I require the I value.

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=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

Answer 1

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)

Result:

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

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)