Find the Nth Occurrence of a Character in a String

,

T-SQL's CHARINDEX() function is a useful for parsing out characters within a string.  However, it only returns the first occurrence of a character.  Oftentimes one needs to locate the Nth instance of a character or a space, which can be a complicated task in standard T-SQL.  This function I wrote, imaginatively named CHARINDEX2(), allows one to do this.  It receives, as arguments, the target string or character, the string to be searched, and the occurrence of the target string/character.

/*
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)
set @counter = 1

if @Occurrence = 1 set @ret = @pos

else
begin

while (@counter < @Occurrence)
begin

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

set @pos = @ret

end

end

RETURN(@ret)

end

Rate

4.64 (11)

Share

Share

Rate

4.64 (11)