SQL里面用自定义Split()完成个性化需求

作者: 达?矢抾哆拉?
来源: 51数据库
2021-11-13

复制代码代码如下:

 
set ansi_nulls on 
go 
set quoted_identifier on 
go 
create function [dbo].[splitstring] 
( 
@input nvarchar(max), 
@separator nvarchar(max)=',', 
@removeemptyentries bit=1 
) 
returns @table table 
( 
[id] int identity(1,1), 
[value] nvarchar(max) 
) 
as 
begin 
declare @index int, @entry nvarchar(max) 
set @index = charindex(@separator,@input) 
while (@index>0) 
begin 
set @entry=ltrim(rtrim(substring(@input, 1, @index-1))) 
if (@removeemptyentries=0) or (@removeemptyentries=1 and @entry<>'') 
begin 
insert into @table([value]) values(@entry) 
end 
set @input = substring(@input, @index+datalength(@separator)/2, len(@input)) 
set @index = charindex(@separator, @input) 
end 
set @entry=ltrim(rtrim(@input)) 
if (@removeemptyentries=0) or (@removeemptyentries=1 and @entry<>'') 
begin 
insert into @table([value]) values(@entry) 
end 
return 
end 

函数、表都建好了，下面调用测试一下吧：

复制代码代码如下:

 
declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max) 
set @str1 = '1,2,3' 
set @str2 = '1###2###3' 
set @str3 = '1###2###3###' 
select [value] from [dbo].[splitstring](@str1, ',', 1) 
select [value] from [dbo].[splitstring](@str2, '###', 1) 
select [value] from [dbo].[splitstring](@str3, '###', 0) 

结果，截个图来看一下：