答对后立即结贴，一简单Sql处理？

[etherealkite]

提供一个字符串B_01_01_01_01_01
现我要求在第2个下划线后插入字母A，则处理后变成B_01_A01_01_01_01
如果我要在第5个下划线后插入字母A，则处理后变成B_01_01_01_01_A01
这个sql怎么写?就是我指定好下划线的序号，用sql怎么样处理能得到我要的结果?

[青锋-SS]

使用函数查找并返回结果吧

[etherealkite]

请使用sql语句回答，谢谢！

[bqb]

create   Function StrSite(@Str varchar(2000) ,@Word varchar(20) ,@TimesNo int)
returns   int
as 
begin 
declare @i int,@Times int
declare @WordLen int
set @Times=0
set @i=1
set @Wordlen=len(@Word)
 
while  (@i< Len(@Str)+1 )   
begin 
if  substring(@Str,@i,@Wordlen)=@word
set   @times=@times+1
--print  '第'+cast(@i as varchar(20)) +'是 : ' +cast(@Times as varchar(20))

if @times=@TimesNo 
break
set @i=@i+1
end 
return  (@i)
end


go

declare @s varchar(200)
select @s='B_01_01_01_01_01'

select stuff(@s,dbo.StrSite(@s,'_',2),1,'_A')


/*
----------------
B_01_A01_01_01_01
*/

drop function dbo.StrSite

[bqb]

create   Function StrSite(@Str varchar(2000) ,@Word varchar(20) ,@TimesNo int)
returns   int
as 
begin 
declare @i int,@Times int
declare @WordLen int
set @Times=0
set @i=1
set @Wordlen=len(@Word)
 
while  (@i< Len(@Str)+1 )   
begin 
if  substring(@Str,@i,@Wordlen)=@word
set   @times=@times+1
--print  '第'+cast(@i as varchar(20)) +'是 : ' +cast(@Times as varchar(20))

if @times=@TimesNo 
break
set @i=@i+1
end 
return  (@i)
end


go

declare @s varchar(200)
select @s='B_01_01_01_01_01'


--现我要求在第2个下划线后插入字母A，则处理后变成B_01_A01_01_01_01 

select stuff(@s,dbo.StrSite(@s,'_',2),1,'_A')

/*
----------------
B_01_A01_01_01_01
*/

---如果我要在第5个下划线后插入字母A，则处理后变成B_01_01_01_01_A01 
select stuff(@s,dbo.StrSite(@s,'_',5),1,'_A')
/*
----------------
B_01_01_01_01_A01
*/




drop function dbo.StrSite

[-狙击手-]

create function f_str(@str varchar(400),@cnt int)
returns varchar(400)
as
begin
    declare @i int,@pos int
	set @I = 1
    set @pos = 1
	set @pos = CHARINDEX ('_',@str,@pos)+1
    while @pos>0
    begin
     
		
        if (@i = @cnt )
        begin
            select @str = stuff(@str, @pos ,0 ,'A')
            break
        end
		set @i = @i + 1
        set @pos =CHARINDEX ('_',@str,@pos)+1
    end
    return @str
end
go




declare @s varchar(100)
set @s = 'B_01_01_01_01_01'

select dbo.f_str(@s,5)
go

drop function f_str
/*
-------------------
B_01_01_01_01_A01

（所影响的行数为 1 行）
*/

[etherealkite]

谢谢大家，我优化了一下，如下：

CREATE FUNCTION [dbo].[f_str] (@str varchar(200),@char char(1),@times int,@ins_str varchar(100))  
--定义在字符串@str中某个出现多次的特定字符中,指定第@times个@char的右边插入字符串@ins_str
RETURNS varchar(300) AS  
BEGIN 
declare @i int,@start int
set @i=0
set @start=0
while (select charindex(@char,@str,@start))>0--查出这个字符在字符串中存在
  begin
    set @i=@i+1
    set @start=(select charindex(@char,@str,@start))+1
    if @i=@times
      begin        
        select @str=stuff(@str,@start,0,@ins_str)
        return @str
      end    
  end
return null
END

select dbo.f_str(@s,'_',2,'A')
---------------
B_01_A01_01_01_01