split select results (one column) into multiple co

Question

I've been trying to work out how to accomplish this, but I think I just don't have the skills to know what to look for in the first place. I'm working with an existing system, and I cannot alter the database schema, nor can I dictate how the users enter data. I have to work with what we have.

Currently, our user is putting stats data into one text field in a table. They are using a standard format of one stat per line, with text qualifiers to 'delimit' the details of the stat. i.e.

<Category> - D:<Description> Q:<Quanitity> V:<Value>
<Category> - D:<Description> Q:<Quanitity> V:<Value>

(there aren't any <> brackets in the actual data... I was just using them to show where details go.)

In the above example, the two stats are held in one text field record... and there are many of these 'stats' records in the table.

Edit: I am using MS SQL Server 2005 I need to create a report for sums of quanitites/values per description.

I have implemented a split user function that I can use on one record to split each line into individual records... but that is as far as I've managed to get.

I need to 'Select Stats from StatsTable', then loop through each stats record, split it up into individual lines, then extract the category, description, quantity and value from each split line, then return all of the results in one table.

Answer 1

I've managed to patch together a nested cursor... It looks like it works.

declare o CURSOR FAST_FORWARD FOR
select comments from EVENT

declare @comment nvarchar(max)

OPEN o FETCH NEXT FROM o into @comment

while @@FETCH_STATUS = 0
BEGIN
Declare @item nvarchar(750)

declare @tbl Table(Category nvarchar(250), Description nvarchar(250), Quantity nvarchar(250), Value DECIMAL(10,2))

declare c CURSOR FAST_FORWARD FOR
SELECT items FROM dbo.Split(@comment, Char(10))

OPEN c FETCH NEXT FROM c into @item

WHILE @@FETCH_STATUS = 0
BEGIN
    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 @item
END
CLOSE c DEALLOCATE c
END
CLOSE o DEALLOCATE o
SELECT * FROM @tbl