如果有一个字符串 eg: “sun,star,moon,clouds”,想要在ms sql中根据给定的分隔符’,’把这个字符串分解成各个元素[sun] [star] [moon] [clouds]，如何实现呢？为此，创建一个function,代码如下:
复制代码 代码如下:
create function [dbo].[split_strbydelimiter](@string varchar(8000), @delimiter char(1))
returns @temptable table (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@string)<1 or @string is null return
while @idx!= 0
begin
set @idx = charindex(@delimiter,@string)
if @idx!=0
set @slice = left(@string,@idx – 1)
else
set @slice = @string
if(len(@slice)>0)
insert into @temptable(items) values(@slice)
set @string = right(@string,len(@string) – @idx)
if len(@string) = 0 break
end
return
end
示例：如果输入
select * from dbo.split_strbydelimiter(‘sun,star,moon,clouds’,’,’)
结果返回
sun
star
moon
clouds
在上面的代码做变形，返回有多少个元素
复制代码 代码如下:
create function [dbo].[getcount_split_strbydelimiter](@string varchar(8000), @delimiter char(1))
returns int
as
begin
declare @temptable table (items varchar(8000))
declare @splitcount int
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@string)<1 or @string is null return 0
while @idx!= 0
begin
set @idx = charindex(@delimiter,@string)
if @idx!=0
set @slice = left(@string,@idx – 1)
else
set @slice = @string
if(len(@slice)>0)
insert into @temptable(items) values(@slice)
set @string = right(@string,len(@string) – @idx)
if len(@string) = 0 break
end
set @splitcount=(select count(*) from @temptable)
return @splitcount
end
示例
select dbo.getcount_split_strbydelimiter(‘sun,star,moon,clouds’,’,’)
结果返回
4