Mod to Find the Nth Occurrence of a character

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
Create  FUNCTION CHARINDEX2
(
@TargetStr varchar(8000), 
@SearchedStr varchar(8000), 
@Occurrence int
)

RETURNS int

as
begin

declare @pos int, @counter int, @ret int

set @pos = CHARINDEX(@TargetStr, @SearchedStr,1)
set @counter = 1
if @Occurrence = 0 set @ret = 1
else
begin
if @Occurrence = 1 set @ret =  @pos


else
begin

while (@counter < @Occurrence) 
begin

select @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)

set @counter = @counter + 1
if @ret <> 0 
set @pos = @ret

end

end

end

RETURN(@ret)

end







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO