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

来源：互联网发布：worknc软件怎么样编辑：程序博客网时间：2024/06/06 05:43

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=0OR @NextIndex IS NULL)
                 SELECT@NextIndex=len(@SplitString)+1;
                 SELECT@ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
                 INSERTINTO @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) --分隔符号
 )
 returnsint
 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), --分隔符号
  @indexint --取第几个元素
 )
 returnsnvarchar(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<>0and @index>@next
  begin
    set@start=@location+@seed
    set@location=charindex(@split,@String,@start)
    set@next=@next+1
  end
  if @location =0select @location =len(@String)+1
   
  returnsubstring(@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

323232f

g3222

阅读全文

0 0