SQL String Parsing

by Web Master 30. July 2015 12:10

I found a slick way to parse a string in SQL at link.  It utilizes the XML features offered by SQL Server to break down the string into components.  Here is the code sample from the link:

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)

This example produces a table with each item's invoice, amount and date.

Tags: , ,

Programming

RecentPosts