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