sql查找某个字符串第N次出现的位置的函数(转帖)

if exists(select 1 from sysobjects where name='char_index')


drop function char_index 





create function char_index(@string varchar(8000),@char varchar(10),@index smallint)


--@string:待查找字符串，@index:查找位置


returns smallint


as


begin


  declare


  @i tinyint,--当前找到第@i个


  @position tinyint--所在位置


  set @position=@index;


  set @i=0;


  while charindex(@char,@string,@position)>0


  begin


    set @position=charindex(@char,@string,@position)+1;


    set @i=@i+1;


    if @i=@index


    begin


     return @position-1;


    end


  end


  return 0;--0表示未找到


end 





select dbo.char_index('sdf_dsf_dfgdg_ertr_erte','f_',2)

 

--如何查找某个字符串第N次出现的位置,


--比如:字符串"sdf_dsf_dfgdg_ertr_erte",要查找"f_"第二次出现的位置


if object_id('f_findstr') is not null


drop function f_findstr


go


create function f_findstr(@s varchar(8000),@find varchar(10),@index int)


returns int


as


begin


declare @startindex int


set @startindex=0


while @index>0


begin


if charindex(@find,@s,@startindex)>0


set @startindex=charindex(@find,@s,@startindex+1)


set @index=@index-1


end


return @startindex


end


go


select dbo.f_findstr('sdf_dsf_dfgdg_ertr_erte','f_',2)

 

use test


go


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F_Str]') and xtype in (N'FN', N'IF', N'TF'))


drop function [dbo].[F_Str]


GO





CREATE FUNCTION dbo.F_Str(


@s varchar(8000),  


@pos int,          


@split varchar(10) 


)RETURNS int


AS


BEGIN


    IF @s IS NULL RETURN(NULL)


    DECLARE @splitlen int,@i int


    SELECT @splitlen=LEN(@split+'a')-2,@i=0


    WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0


        SELECT @pos=@pos-1,@i=@i+CHARINDEX(@split,@s+@split)+@splitlen,


            @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')


    RETURN(@i+CHARINDEX(@split,@s+@split))


END


GO


declare @s nvarchar(100)


select @s='sdf_dsf_dfgdg_ertr_erte'


select dbo.F_Str(@s,2,'f_')








-----------


7





(1 行受影响)

方法4

if exists(select 1 from sysobjects where name='char_index')


drop function char_index 


go


create function char_index(@string varchar(8000),@char varchar(10),@index smallint)


--@string:待查找字符串，@index:查找位置


returns smallint


as


begin


  declare


  @i int,--当前找到第@i个


  @position int--所在位置


  set @position=1;


  set @i=0;


  while charindex(@char,@string,@position)>0


  begin


    set @position=charindex(@char,@string,@position)+1;


    set @i=@i+1;


    if @i=@index


    begin


     return @position-1;


    end


  end


  return 0;--0表示未找到


end 


go