sql 字符串 切割函数 FUN_Split

IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N‘[dbo].[FUN_Split]‘)
                    AND OBJECTPROPERTY(id, N‘IsTableFunction‘) = 1 )
    DROP FUNCTION [dbo].[FUN_Split]
GO
--分割字符串函数
Create FUNCTION [dbo].[FUN_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

--select * FROm dbo.FUN_Split(‘111,b2222,323232,32d,e,323232f,g3222‘, ‘,‘)