Find Nth Occurrence of Character Function V2

,

This function was originally contributed by another visitor and I corrected the logic to return the max position of the character if occurrence is greater than the max.

I hope everyone finds it useful.

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
SELECT dbo.CHARINDEX2('b', 'abbabba', 5)
returns the last location of an occurrence of 'b'
which is 6, since there were not 5 occurrences.
I thought this was better than returing zero when you are trying to find the last occurrence
*/
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 @pos = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)		
	set @counter = @counter + 1
	if @pos <> 0 
		set @ret = @pos
end

end

end

RETURN(@ret)

end







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate