如何将此逗号分隔到新表中

你好,有经验的SQL's。。。

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               

正如你所见,这是相当可怕的。每一个新行都会添加一个尾随逗号,然后在应用程序内部对其进行解析,并忽略“blank”的最后一个逗号条目(因为这将是最后一个逗号后面的空空格)。我已经通过Google找到了几个几乎可以完成这项工作的例程,但是由于后面逗号的性质,例程会选取最后一个条目,并将其附加到下一个后续条目中,从而在最后一行给出一个不正确的条目。这有时是空行,但通常是下一行的值。

不管怎样,下面是我正在使用的程序。希望有人能看到一个合适的“如果”语句测试,将它引导到正确的方向。不管怎样,函数:

    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

以及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,',') 
             FETCH NEXT FROM CUR_TABLE
             INTO @bookingdetailid,@amendmentnote
         END
         CLOSE CUR_TABLE
         DEALLOCATE CUR_TABLE
         SELECT * FROM #tmp 
         RETURN
    END

-以下是我为了让它工作而做的改变:

    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

用法:

DECLARE @V_RC INT
EXEC @V_RC = DBO.USP_SPLIT_COLUMN

如果没有足够的信息,就告诉我。

吉姆