SQL-将字符串按分割符拆分返回表

例子：select * from F_Split('1,2,3,4,5',',')

创建函数：

---

Create FUNCTION [dbo].[F_Split]

(

    @SplitString nvarchar(max),  --源字符串

    @Separator nvarchar(10)=' '  --分隔符号，默认为空格

)

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

GO

---