ALTER FUNCTION dbo.fn_Split

(

    @Expression    VARCHAR(8000),

    @Delimiter    VARCHAR(10)

)

RETURNS @table_Value    TABLE

(

    SortNo    INT IDENTITY(1,1) NOT NULL,

    Value    VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ''

)

AS

BEGIN

    --============ DECLARE LOCAL VARIABLE TO USE  ===============================

    DECLARE @intEndpos    BIGINT

    DECLARE @strTemp        VARCHAR(100)

    --============ INITIALIZE  THE VALUE OF VARIABLE ==============================

    SET @intEndpos = 0

    SET @strTemp = ''

    --============= CHECK INPUT DATA VALIDATE ==========================

    IF LEN( LTRIM(RTRIM(@Expression ) ) ) = 0

    BEGIN

        RETURN

    END

    IF LEN(@Delimiter) = 0

    BEGIN

        RETURN

    END

    --============= Split and INSERT ========================================

    IF RIGHT ( @Expression, 1) = @Delimiter

        SET @Expression = LEFT ( @Expression , LEN (@Expression) -1 )

    WHILE LEN(@Expression) > 0

    BEGIN

        SET @intEndpos = CHARINDEX(@Delimiter,@Expression,1)

        IF @intEndpos > 0

        BEGIN

            SET @strTemp = SUBSTRING(@Expression , 1 , @intEndpos -1)

            INSERT INTO @table_Value(Value) VALUES (@strTemp)

            SET @Expression = SUBSTRING ( @Expression , @intEndpos + 1, LEN (@Expression) - @intEndpos)

        END

        ELSE

        BEGIN

            INSERT INTO @table_Value(Value) VALUES (@Expression)

            SET @Expression = ''

        END

    END

    RETURN

END