第二篇 重要函数应用3

进一步改造目标2: 可自定义连接符,而不局限只能是逗号(“,”)。

引用金蝶K3的行转列函数,功能更强,可以不限逗号;

/******************************************************************************

 * Function Name: fn_SplitStringToTable                                                                                      

 * Description:  拆分一个字符串       *

 * Parameters:    @InputString –要拆分的字符串                                *

 *                @Delimeter – 自定义的连接符                                      *

 ******************************************************************************/

ALTER  FUNCTION fn_SplitStringToTable

(

    @InputString VARCHAR(8000),

    @Delimeter   CHAR(1)

)

RETURNS @Table TABLE

(

    FSequence INT IDENTITY(1, 1),

    FValue    VARCHAR(511),

    FPosition INT,

    FLength   INT

)

BEGIN

    DECLARE @Position INT

    

    SELECT @Position = 1

    

    IF RIGHT(@InputString, LEN(@Delimeter)) <> @Delimeter

        SET @InputString = @InputString + @Delimeter

    

    WHILE SUBSTRING(@InputString, @Position, 1) = CHAR(10) OR

          SUBSTRING(@InputString, @Position, 1) = CHAR(13)

    BEGIN

        SELECT @Position = @Position + 1

    END

    

    WHILE @Position <= CHARINDEX(@Delimeter, @InputString, @Position)

    BEGIN

        INSERT INTO @Table(FValue, FPosition, FLength)

             VALUES (RTRIM(LTRIM(SUBSTRING(@InputString,

                                           @Position,

                                           CHARINDEX(@Delimeter,

                                                     @InputString,

                                                     @Position) - @Position))), @Position, CHARINDEX(@Delimeter, @InputString, @Position) - @Position)

        SELECT @Position = CHARINDEX(@Delimeter, @InputString, @Position) + 1

        

        WHILE SUBSTRING(@InputString, @Position, 1) = CHAR(10) OR

              SUBSTRING(@InputString, @Position, 1) = CHAR(13)

        BEGIN

            SELECT @Position = @Position + 1

        END

    END

    RETURN

END

GO

--测试

select * FROM dbo.fn_SplitStringToTable('SSSSSS,ASDF-ASDFASDF','-')