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  select * FROm dbo.SplitToTable('111,b2222,323232,32d,e,323232f,g3222', ',')

結果為

id          value ----------- ---------------------------------------

1           111

2           b2222

3           323232

4           32d

5           e

6           323232f

7           g3222

(7 行受影響)

 

使用迴圈的方法

首先GetSplitLength函數返回分割後的字元數組的長度。

　　

Create function [dbo].[GetSplitLength] (  @String nvarchar(max),  --要分割的字串  @Split nvarchar(10)  --分隔字元號 ) returns int as begin  declare @location int  declare @start int  declare @length int   set @String=ltrim(rtrim(@String))  set @location=charindex(@split,@String)  set @length=1  while @location<>0  begin    set @start=@location+1    set @location=charindex(@split,@String,@start)    set @length=@length+1  end  return @length end

　　

select dbo.GetSplitLength('111,b2222,323232,32d,e,323232f,g3222',',')

結果為7。

 

GetSplitOfIndex函數是按順序分別擷取分割後的字串。

ALTER function [dbo].[GetSplitOfIndex] (  @String nvarchar(max),  --要分割的字串  @split nvarchar(10),  --分隔字元號  @index int --取第幾個元素 ) returns nvarchar(1024) as begin  declare @location int  declare @start int  declare @next int  declare @seed int   set @String=ltrim(rtrim(@String))  set @start=1  set @next=1  set @seed=len(@split)    set @location=charindex(@split,@String)  while @location<>0 and @index>@next  begin    set @start=@location+@seed    set @location=charindex(@split,@String,@start)    set @next=@next+1  end  if @location =0 select @location =len(@String)+1     return substring(@String,@start,@location-@start) end

　　select dbo.GetSplitOfIndex('111,b2222,323232,32d,e,323232f,g3222',',', 3)

結果323232。

 DECLARE @Tags nvarchar(max); SELECT @Tags='111,b2222,323232,32d,e,323232f,g3222'; DECLARE @Tag nvarchar(1000) DECLARE @next int; set @next=1  DECLARE @Length int; SELECT @Length=dbo.GetSplitLength(@Tags,',') while @next<=@Length begin     SET @Tag = left(dbo.GetSplitOfIndex(@Tags,',',@next), 16);     print @Tag     SET @Next=@Next+1; END

　　

結果為：

111

b2222

323232

32d

e

323232f

g3222