Analizando una cadena SQL

Tengo esta cadena muy larga en MSSQL que necesito el valor I.

Yo represento la factura, A representa la cantidad, D representa la 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 = 28 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

Este es uno de los casos más aterradores.

Cada uno de estos son numbers de factura que tienen valores relevantes que usaré para vincular a otra transacción. Realmente agradecería que alguien pudiera explicar la mejor manera de hacerlo sin hacer una aplicación si es posible

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) 

Resultado:

 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 

Para SQL Server 2005 necesita usar datetime en lugar de 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) 

Para leer desde una tabla, debes hacerlo así.

 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) 

No sé si esto lo ayudará, pero aquí hay una function de analizador de cadenas compatible con SQL Server … Llámalo así:

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

la function es:

 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 

Utilizo una function en SQL para dividir cadenas, tal vez ese podría ser su punto de partida:

 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