T-SQL 截取字符串到table

今天自己遇到个问题,随手写了点东西,觉得挺实用,拿出来跟大家分享!

--将以逗号格开的字符串 用指定字符或符号格开转成TABLE

 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
GO

posted on 2011-12-16 11:52  其小本  阅读(381)  评论(0编辑  收藏  举报