How to separate this comma delim column to new table

Hello experienced SQL'rs...

I've got a little issue (SQLServer 2005) that I've run into with a legacy table (I'm honestly not trying to cover my embarrassment here - it is an old table :-)). Basically, for reasons best known to the original coder, one of the columns is being used to record changes of state to the object referred to in the table (in this case, a property booking). This single column (amendmentnote) is being appended to as a comma delimited list and then when being pulled back into the application (a .net app). This string is parsed out into individual lines. So far so 'good'. However, I've now been tasked with normalizing this column out to a separate table as individual rows. Below is the representation of how this looks and what I hope to achieve:

current bookingdetail table
bookingdetailid | amendmentnote | other fields....
-------------------------------------------------------------------
145               16/07/2010 14:15:02: New,29/07/2010 15:09:42: Booking status change from On Option to Cancelled,
146               19/07/2010 12:34:05: New,


proposed denormailzed booking_amendment_notes table
bookingdetailid | amendmentnote
-------------------------------------------------------------------
145               16/07/2010 14:15:02: New
145               Booking status change from On Option to Cancelled 
145               
146               19/07/2010 12:34:05: New
146               

As you can see, it's pretty horrible. Each new line is added with a trailing comma and this is then parsed inside the app and the 'blank' final comma entry is ignored (as this would be a null space after the final comma). I've sourced a couple of routines that 'almost' do the job via Google, but due to the nature of that trailing comma the routine picks up the last entry and appends it to the next subsequent entry, thus giving an incorrect entry on every last row. This is sometimes a null row but is often the value of the next row.

Anyway, here are the routines that I'm using. Hopefully, someone will be able to see an appropriate 'if' statement test that will steer it in the correct direction. Anyway, the function:

    ALTER FUNCTION dbo.fn_Split
    (@sText varchar(8000), @sDelim varchar(20) = ' ')
    RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
    AS
    BEGIN
    DECLARE @idx smallint,
          @value varchar(8000),
          @bcontinue bit,
          @iStrike smallint,
          @iDelimlength tinyint

    IF @sDelim = 'Space'
          BEGIN
          SET @sDelim = ' '
          END

    SET @idx = 0
    SET @sText = LTrim(RTrim(@sText))
    SET @iDelimlength = DATALENGTH(@sDelim)
    SET @bcontinue = 1

    IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
          BEGIN
          WHILE @bcontinue = 1
                BEGIN

    --If you can find the delimiter in the text, retrieve the first element and
    --insert it with its index into the return table.

                IF CHARINDEX(@sDelim, @sText)>0
                      BEGIN
                      SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                            BEGIN
                            INSERT @retArray (idx, value)
                            VALUES (@idx, @value)
                            END

    --Trim the element and its delimiter from the front of the string.
                      --Increment the index and loop.
    SET @iStrike = DATALENGTH(@value) + @iDelimlength
                      SET @idx = @idx + 1
                      SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

                      END
                ELSE
                      BEGIN
    --If you can’t find the delimiter in the text, @sText is the last value in
    --@retArray.
     SET @value = @sText
                            BEGIN
                            INSERT @retArray (idx, value)
                            VALUES (@idx, @value)
                            END
                      --Exit the WHILE loop.
    SET @bcontinue = 0
                      END
                END
          END
    ELSE
          BEGIN
          WHILE @bcontinue=1
                BEGIN
                --If the delimiter is an empty string, check for remaining text
                --instead of a delimiter. Insert the first character into the
                --retArray table. Trim the character from the front of the string.
    --Increment the index and loop.
                IF DATALENGTH(@sText)>1
                      BEGIN
                      SET @value = SUBSTRING(@sText,1,1)
                            BEGIN
                            INSERT @retArray (idx, value)
                            VALUES (@idx, @value)
                            END
                      SET @idx = @idx+1
                      SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

                      END
                ELSE
                      BEGIN
                      --One character remains.
                      --Insert the character, and exit the WHILE loop.
                      INSERT @retArray (idx, value)
                      VALUES (@idx, @sText)
                      SET @bcontinue = 0      
                      END
          END

    END

    RETURN
    END

and the sp:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[USP_SPLIT_COLUMN] AS
    DECLARE CUR_TABLE CURSOR FOR
    SELECT bookingdetailid,amendmentnote
    FROM DBO.bookingdetail
    DECLARE @bookingdetailid varchar(50),
    @amendmentnote varchar(8000)

    BEGIN
         CREATE TABLE #tmp (bookingdetailid varchar(50),amendmentnote varchar(7800))
         OPEN CUR_TABLE
         FETCH NEXT FROM CUR_TABLE
         INTO @bookingdetailid,@amendmentnote
         WHILE @@FETCH_STATUS = 0 
         BEGIN
             INSERT INTO #tmp(bookingdetailid,amendmentnote)
             SELECT @bookingdetailid, value amendmentnote
             FROM DBO.FN_SPLIT(@amendmentnote,',') 
      开发者_Go百科       FETCH NEXT FROM CUR_TABLE
             INTO @bookingdetailid,@amendmentnote
         END
         CLOSE CUR_TABLE
         DEALLOCATE CUR_TABLE
         SELECT * FROM #tmp 
         RETURN
    END

[update] - here's what i changed to get it working:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[USP_SPLIT_COLUMN] AS
    DECLARE CUR_TABLE CURSOR FOR
    SELECT bookingdetailid,amendmentnote
    FROM DBO.bookingdetail
    DECLARE @bookingdetailid varchar(50),
    @amendmentnote varchar(8000)

    BEGIN
         CREATE TABLE #tmp (bookingdetailid varchar(50),amendmentnote varchar(7800))
         OPEN CUR_TABLE
         FETCH NEXT FROM CUR_TABLE
         INTO @bookingdetailid,@amendmentnote
           select @amendmentnote = left(@amendmentnote,Len(@amendmentnote)-1) 
         WHILE @@FETCH_STATUS = 0 
         BEGIN
             INSERT INTO #tmp(bookingdetailid,amendmentnote)
             SELECT @bookingdetailid, value amendmentnote
             FROM DBO.FN_SPLIT(@amendmentnote,',') 
             FETCH NEXT FROM CUR_TABLE
             INTO @bookingdetailid,@amendmentnote
             if right(@amendmentnote,1)=','
             begin
             select @amendmentnote = left(@amendmentnote,Len(@amendmentnote)-1) 
             end
         END
         CLOSE CUR_TABLE
         DEALLOCATE CUR_TABLE
         SELECT * FROM #tmp 
         RETURN
    END

usage:

DECLARE @V_RC INT
EXEC @V_RC = DBO.USP_SPLIT_COLUMN

If there's not enough info, just let me know.

Thanks

jim

btw - it's not possible to run the routine in .net and save it out to the table for a variety of reasons, so it has to be a SQL solution.

You just need a TRIM(my_horrible_string, ',') before the splitting routine