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