sqlserver 循环取出字符串按照某个字符分割后的第几个串

create                 function uf_getStrBySplit(@old_str varchar(8000),@split varchar(50),@pos int) returns varchar(200)

as

/*

功能描述：返回分割后的字符串的第几个字符串

参数说明:@old_str:原字符串

@split:分隔符

@pos:第几个

*/

begin

declare @rtn varchar(200),

@li_p int

select @rtn=''

select @old_str=ltrim(rtrim(@old_str))+@split

select @li_p=0

while charindex(@split,@old_str)>1

begin

select @li_p=@li_p + 1

if @li_p=@pos

begin

select @rtn=left(@old_str,charindex(@split,@old_str) - 1)

return @rtn

end

select @old_str=right(@old_str,len(@old_str) - charindex(@split,@old_str))

end

return @rtn

end

 

create procedure usp_upd_stadium_stadium(@ids varchar(100))

as

/*

--测试 

execute usp_upd_stadium_stadium '0001,0002,0003,0004,'

*/

begin

declare @i int,

@cnt int,

@id varchar(4)

select @i=0,@cnt=0

select @cnt=len(replace(@ids,',',',_')) - len(@ids)

while @i<@cnt

begin

select @i=@i+1

select @id = dbo.uf_getStrBySplit(@ids,',',@i)

print @id

end

end

转载于:https://www.cnblogs.com/kuailewangzi1212/archive/2011/06/20/2085054.html