CREATE  FUNCTION [dbo].[fnQuerySplit]

    (

      @string VARCHAR(MAX) ,--待分割字符串

      @separator VARCHAR(255)--分割符

    )

RETURNS @array TABLE ( item VARCHAR(255) )

AS

    BEGIN

        DECLARE @begin INT ,

            @end INT ,

            @item VARCHAR(255);

        SET @begin = 1;

        SET @end = CHARINDEX(@separator, @string, @begin);

        WHILE ( @end <> 0 )

            BEGIN

                SET @item = SUBSTRING(@string, @begin, @end - @begin);

                INSERT  INTO @array

                        ( item )

                VALUES  ( @item );

                SET @begin = @end + 1;

                SET @end = CHARINDEX(@separator, @string, @begin);

            END;

        SET @item = SUBSTRING(@string, @begin, LEN(@string) + 1 - @begin);

        IF ( LEN(@item) > 0 )

            INSERT  INTO @array

                    ( item

                    )

            VALUES  ( SUBSTRING(@string, @begin, LEN(@string) + 1 - @begin)

                    );

        RETURN;

    END;

CREATE  FUNCTION [dbo].[Fun_String2Table]

    (

      @str NVARCHAR(MAX) ,

      @split NVARCHAR(10)

    )

RETURNS @table TABLE ( [item] NVARCHAR(MAX) )

AS

    BEGIN

        IF LEN(@split) = 0

            BEGIN

                SET @split = N',';

            END;

        DECLARE @xml XML;

        SET @xml = CONVERT(XML, '<x><![CDATA['

            + REPLACE(CONVERT(VARCHAR(MAX), @str), @split,

                      ']]></x><x><![CDATA[') + ']]></x>');

        INSERT  INTO @table

                SELECT  item

                FROM    ( SELECT    c.value('text()[1]', 'nvarchar(4000)') [item]

                          FROM      @xml.nodes('/x') t ( c )

                        ) t

                WHERE   item IS NOT NULL;

        RETURN;

    END;

CREATE FUNCTION [dbo].[f_split]

    (

      @SourceSql NVARCHAR(MAX) ,

      @StrSeprate NVARCHAR(10)

    )

RETURNS @temp TABLE ( value NVARCHAR(100) )

AS

    BEGIN

        DECLARE @i INT;

        SET @SourceSql = RTRIM(LTRIM(@SourceSql));

        SET @i = CHARINDEX(@StrSeprate, @SourceSql);

        WHILE @i >= 1

            BEGIN

                INSERT  @temp

                VALUES  ( LEFT(@SourceSql, @i - 1) );

                SET @SourceSql = SUBSTRING(@SourceSql, @i + 1,

                                           LEN(@SourceSql) - @i);

                SET @i = CHARINDEX(@StrSeprate, @SourceSql);

            END;

        IF @SourceSql <> '\'

            INSERT  @temp

            VALUES  ( @SourceSql );

        RETURN;

    END;