-- select * from [dbo].[SplitToTable]('ADSF','|')

-- 分解字符串

ALTER FUNCTION [dbo].[SplitToTable]

(

    @SplitString varchar(max),

    @Separator char(1)

)

RETURNS @SplitStringsTable TABLE

(

[id] int identity(1,1),

[value] nvarchar(max)

)

AS

BEGIN

    DECLARE @CurrentIndex int;

    DECLARE @NextIndex int;

    DECLARE @ReturnText nvarchar(max);

    SELECT @CurrentIndex=1;

    WHILE(@CurrentIndex<=len(@SplitString))

        BEGIN

            SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);

            IF(@NextIndex=0 OR @NextIndex IS NULL)

                SELECT @NextIndex=len(@SplitString)+1;

                SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);

                INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);

                SELECT @CurrentIndex=@NextIndex+1;

            END

    RETURN;

END