|
alter function sql_split
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @split_str varchar(1024)
declare @location int
declare @start int
declare @next int
declare @seed int
set @split_str=ltrim(rtrim(@str))
if left(@split_str,1)=@split
set @split_str=right(@split_str,len(@split_str)-1)
if right(@split_str,1)=@split
set @split_str=left(@split_str,len(@split_str)-1)
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@split_str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@split_str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@split_str)+1
--return @split_str+' '+substring(@split_str,@start,@location-@start)
return substring(@split_str,@start,@location-@start)
end
select dbo.sql_split('1,2,3,4,5,6,7,8,9',',',5)
返回 5
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/arrow_gx/archive/2011/01/20/6154544.aspx
|
