建立sql数组的一个函数

< DOCTYPE html PUBLIC -WCDTD XHTML StrictEN httpwwwworgTRxhtmlDTDxhtml-strictdtd>

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================

-- Author:      netcorner

-- Create date:   3/10/2008

-- Description: Split string variant with type of ntext

-- =============================================

Create FUNCTION [dbo].[split]

(

   @text ntext,

   @delimiter char(1)

)

RETURNS @arrayTable TABLE(Idx bigint, [Value] nvarchar(200))

AS

BEGIN

     DECLARE @splitlen int
DECLARE @tmp varchar(1024)

    SET @splitlen = 4000

    DECLARE @Idx int SET @Idx = 0

    -- 定义取子串的起始位置

    DECLARE @textsplit bigint

    SET @textsplit = 1

    WHILE( @textsplit <= DATALENGTH(@text) )

    BEGIN

        -- 由于许多字符串处理函数无法用于ntext数据类型

        -- 所以需要循环按批处理ntext字符串，一批取出

        -- 个字符放入nvarchar(4000)类型的变量中.

        DECLARE @string nvarchar(4000)

        SELECT @string = SUBSTRING(@text,@textsplit,@splitlen)

        -- 能够取出满个字符

        IF LEN(@string) = @splitlen

        BEGIN

            -- 确保取出的个字符是完整的由分隔符隔开的字符串组合

            DECLARE @lastcomma int

            SELECT @lastcomma = CHARINDEX(@delimiter,REVERSE(@string),1)

            -- 最后一个分隔符后面的字符串不完整，应抛弃

            IF @lastcomma > 0

            BEGIN

                SELECT @string = SUBSTRING(@string,1,@splitlen - @lastcomma)

                -- 设置下一次从@text取字符的起始位置

                SELECT @textsplit = @textsplit + @splitlen - @lastcomma + 1

            END

            -- 最后一个分隔符后面的字符串完整.

            ELSE

            BEGIN

                SELECT @textsplit = @textsplit + @splitlen + 1

            END

        END

        -- 取出不满个字符

        ELSE

        BEGIN

            SELECT @textsplit = @textsplit + @splitlen + 1

        END

      

        -- 解析@string，取出以分隔符为界限的子字符串

        DECLARE @i1 int SET @i1 = 1

        DECLARE @i2 int SET @i2 = 1

        WHILE @i1 <= LEN(@string)

        BEGIN

            SET @i2 = CHARINDEX(@delimiter,@string,@i1)
            IF @i2 = 0
                SET @i2 = LEN(@string) + 1
    set @tmp=SUBSTRING(@string,@i1,@i2-@i1)
    if(@tmp='')
     INSERT @arrayTable (Idx) select @Idx
    else
    begin
     INSERT @arrayTable (Idx, Value)
     SELECT @Idx, @tmp
    end
            SET @i1 = @i2 + 1

            SET @Idx = @Idx + 1

        END

    END

    RETURN

END

#数据库技术