/**/
/*
方法很多,这里简单写一个
返回@find在@str中第(@n)次出现的位置。没有第(@n)次返回0。
*/
create function fn_find( @find varchar ( 8000 ), @str varchar ( 8000 ), @n smallint )
returns int
as
begin
if @n < 1 return ( 0 )
declare @start smallint , @count smallint , @index smallint , @len smallint
set @index = charindex ( @find , @str )
if @index = 0 return ( 0 )
else select @count = 1 , @len = len ( @find )
while @index > 0 and @count < @n
begin
set @start = @index + @len
select @index = charindex ( @find , @str , @start ), @count = @count + 1
end
if @count < @n set @index = 0
return ( @index )
end
go
declare @str varchar ( 100 )
set @str = ' A,B,C,D,A,B,C,D,C,D,B,A,C,E '
select dbo.fn_find( ' A ' , @str , 1 ) as one, dbo.fn_find( ' A ' , @str , 2 ) as two, dbo.fn_find( ' A ' , @str , 3 ) as three, dbo.fn_find( ' A ' , @str , 4 ) as four
/**/ /*
one two three four
----------- ----------- ----------- -----------
1 9 23 0
*/
方法很多,这里简单写一个
返回@find在@str中第(@n)次出现的位置。没有第(@n)次返回0。
*/
create function fn_find( @find varchar ( 8000 ), @str varchar ( 8000 ), @n smallint )
returns int
as
begin
if @n < 1 return ( 0 )
declare @start smallint , @count smallint , @index smallint , @len smallint
set @index = charindex ( @find , @str )
if @index = 0 return ( 0 )
else select @count = 1 , @len = len ( @find )
while @index > 0 and @count < @n
begin
set @start = @index + @len
select @index = charindex ( @find , @str , @start ), @count = @count + 1
end
if @count < @n set @index = 0
return ( @index )
end
go
declare @str varchar ( 100 )
set @str = ' A,B,C,D,A,B,C,D,C,D,B,A,C,E '
select dbo.fn_find( ' A ' , @str , 1 ) as one, dbo.fn_find( ' A ' , @str , 2 ) as two, dbo.fn_find( ' A ' , @str , 3 ) as three, dbo.fn_find( ' A ' , @str , 4 ) as four
/**/ /*
one two three four
----------- ----------- ----------- -----------
1 9 23 0
*/