Parsing a string SQL

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

开发者_StackOverflow中文版

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)

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)

Don't know if this will help you, but here is a sql-server compatible string parser function... Call it like this:

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

the function is:

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

I use a function in SQL to split strings, maybe that 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