sql 字符串 截取

不包含 Separator

ALTER FUNCTION [dbo].[SplitToTableNotContainSeparator]

 (
     @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+LEN(@Separator));
            IF(@NextIndex=0 OR @NextIndex IS NULL)
            begin
                set @NextIndex=len(@SplitString)+1;
            end 
                       
            if(@CurrentIndex=1)
begin
set @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
end
            else
begin            
SELECT @ReturnText=substring(@SplitString,@CurrentIndex+LEN(@Separator),@NextIndex-@CurrentIndex-LEN(@Separator));
end
            if @ReturnText<>''
begin
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
end
            set @CurrentIndex=@NextIndex;
        END
    RETURN;

END

包含 Separator

ALTER FUNCTION [dbo].[SplitToTableContainSeparator]
 (
     @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);
    set @CurrentIndex=1;
    WHILE(@CurrentIndex<=len(@SplitString))
        BEGIN
            set @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex+LEN(@Separator));
            IF(@NextIndex=0 OR @NextIndex IS NULL)
begin
set @NextIndex=len(@SplitString)+1;
end
            set @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
            if @ReturnText<>''
begin
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
end
            set @CurrentIndex=@NextIndex;
            END
    RETURN;
END