此函数实现sql中的字段中字符串的split方法

create FUNCTION [dbo].[SplitToTable]
(
      @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

转载于:https://www.cnblogs.com/TNSSTAR/archive/2012/09/25/2701088.html