SQL SERVER分割字符串

wxin

1、使用指定的字符串分割，返回分割后元素的个数
create function Get_StrLength
(
@str varchar(1024),
@split varchar(10)
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
调用方法：select dbo.Get_StrLength('7,5,6,7,a,f,d',',')
 
2、按指定符号分割字符串，返回分割后指定索引的第几个元素，像数组一样
 
create function Get_StrOfIndex
(
@str varchar(1024),
@split varchar(10),
@index int
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
--说明：这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。

if @location=0
select @location=len(@str)+1
return substring(@str,@start,@location-@start)
end
调用方法：select dbo.Get_StrOfIndex('8,9,3,3,4,5',',',26)
3、结合上边两个函数，返回分割后的元素
create function f_splitstr
(
@SourceSql varchar(8000),
@strSeprate varchar(100)
)
returns @temp table (F1 varchar(100))
as
begin
declare @ch as varchar(100)
set @SourceSql=@SourceSql+@StrSeprate
while(@SourceSql<>'')
begin
set
@ch=left(@SourceSql,Charindex(',',@SourceSql,1)-1)
insert @temp values(@ch)
set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')
end
return
end
调用方法：select * from f_splitstr('1,2,3,4,5,6',',')