我已经设法将一个嵌套的游标拼凑在一起了……看起来很有效。
declare o CURSOR FAST_FORWARD FORselect comments from EVENTdeclare @comment nvarchar(max)OPEN o FETCH NEXT FROM o into @commentwhile @@FETCH_STATUS = 0BEGINDeclare @item nvarchar(750)declare @tbl Table(Category nvarchar(250), Description nvarchar(250), Quantity nvarchar(250), Value DECIMAL(10,2))declare c CURSOR FAST_FORWARD FORSELECt items FROM dbo.Split(@comment, Char(10))OPEN c FETCH NEXT FROM c into @itemWHILE @@FETCH_STATUS = 0BEGIN set @item = @item + ':' insert into @tbl Values (LTRIm(RTRIm(SUBSTRINg(@item, 1, CHARINDEX(' - ', @item) - 1))), CASE when @item like '%D:%' Then LTRIm(RTRIm(SUBSTRINg(@item, CHARINDEX('D:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('D:', @item)+2) - CHARINDEX('D:', @item) - 3))) else '' end, CASE when @item like '%Q:%' Then LTRIm(RTRIm(SUBSTRINg(@item, CHARINDEX('Q:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('Q:', @item)+2) - CHARINDEX('Q:', @item) - 3))) else '1' end, CASE when @item like '%V:%' Then ConVERT(DECIMAL(10,2),dbo.RemoveNonNumericCharacters(LTRIm(RTRIm(SUBSTRINg(@item, CHARINDEX('V:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('V:', @item)+2) - CHARINDEX('V:', @item) - 3))))) else 0 end) FETCH NEXT FROM c into @itemENDCLOSE c DEALLOCATE cENDCLOSE o DEALLOCATE oSELECt * FROM @tbl
