SQL自定义函数split 将数组(分隔字符串)返回阵列（表）

来源：互联网发布：小孩上网监控软件编辑：程序博客网时间：2024/06/07 00:19

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函数是按顺序分别获取分割后的字符串。

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<=@Lengthbegin    SET @Tag = left(dbo.GetSplitOfIndex(@Tags,',',@next), 16);    print @Tag    SET @Next=@Next+1;END

结果为：

111

b2222

323232

32d

323232f

g3222

0 0