SQL 查找字符在字符串出现位置

SQL code
/*
方法很多,这里简单写一个
返回@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
相关文章
相关标签/搜索