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