SQL parsing

Question

I have a very long string in MSSQL that requires an I.

I represent the invoice, A represents the amount, D represents the 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 worst cases.

Each of them represents account numbers that have corresponding values, which I will use to refer to another transaction. I would really appreciate it if someone could explain the best way to do this without making an application, 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 as follows.

 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, TNvalue('substring(value[1],3)', 'int') as Invoice, TNvalue('substring(value[2],3)', 'money') as Amount, TNvalue('substring(value[3],3)', 'date') as [Date] from @YourTable as Y cross apply (select cast('<item><value>'+replace(replace(Ys, ';','</value><value>'), '|','</value></item><item><value>')+'</value></item>' as xml)) as X(XMLCol) cross apply X.XMLCol.nodes('item') as T(N) 

Answer 2

I don’t know if this will help you, but here is the parser function compatible with the sql server ... Call it this:

 Select * From dbo.ParseTextString([Your long text string here], '|') 

function:

 Create FUNCTION [dbo].[ParseTextString] (@S Text, @delim VarChar(5)) Returns @tOut Table (ValNum Integer Identity Primary Key, sVal VarChar(8000)) As Begin Declare @dLLen TinyInt -- Length of delimiter Declare @sWin VarChar(8000)-- Will Contain Window into text string Declare @wLen Integer -- Length of Window Declare @wLast TinyInt -- Boolean to indicate processing Last Window Declare @wPos Integer -- Start Position of Window within Text String Declare @sVal VarChar(8000)-- String Data to insert into output Table Declare @BtchSiz Integer -- Maximum Size of Window Set @BtchSiz = 7900 -- (Reset to smaller values to test routine) Declare @dPos Integer -- Position within Window of next Delimiter Declare @Strt Integer -- Start Position of each data value within Window -- ------------------------------------------------------------------------- -- --------------------------- If @delim is Null Set @delim = '|' If DataLength(@S) = 0 Or Substring(@S, 1, @BtchSiz) = @delim Return -- --------------------------- Select @dLLen = Len(@delim), @Strt = 1, @wPos = 1, @sWin = Substring(@S, 1, @BtchSiz) Select @wLen = Len(@sWin), @wLast = Case When Len(@sWin) = @BtchSiz Then 0 Else 1 End, @dPos = CharIndex(@delim, @sWin, @Strt) -- ---------------------------- While @Strt <= @wLen Begin If @dPos = 0 Begin -- No More delimiters in window If @wLast = 1 Set @dPos = @wLen + 1 Else Begin Set @wPos = @wPos + @Strt - 1 Set @sWin = Substring(@S, @wPos, @BtchSiz) -- ---------------------------------------- Select @wLen = Len(@sWin), @Strt = 1, @wLast = Case When Len(@sWin) = @BtchSiz Then 0 Else 1 End, @dPos = CharIndex(@delim, @sWin, 1) If @dPos = 0 Set @dPos = @wLen + 1 End End -- ------------------------------- Set @sVal = LTrim(Substring(@sWin, @Strt, @dPos - @Strt)) Insert @tOut (sVal) Values (@sVal) -- ------------------------------- -- Move @Strt to char after last delimiter Set @Strt = @dPos + @dLLen Set @dPos = CharIndex(@delim, @sWin, @Strt) End Return End 

Answer 3

I use a function in SQL to split the strings, maybe this could be your starting point:

 CREATE Function [dbo].[CsvToVarchar] ( @Array varchar(4000)) returns @IntTable table (CharsValue VARCHAR(40)) AS begin declare @separator varchar(5) set @separator = ',' declare @separator_position int declare @array_value varchar(4000) set @array = @array + @separator while patindex('%' +@separator +'%' , @array) <> 0 begin select @separator_position = patindex('%' +@separator +'%' , @array) select @array_value = left(@array, @separator_position - 1) Insert @IntTable Values (@array_value) select @array = stuff(@array, 1, @separator_position, '') end return end