Find Nth Occurrence of Character Function V3

,

In the tradition of best practice & code improvement, a third iteration of a script that finds the Nth occurrence of a Target string within another string (Original author unknown, V2 author vextant).

This version returns zero if an Nth occurrence is not found (similar to V1), but also provides the ability to search the string Right to Left so that you can find the last occurrence, 2nd to last occurrence, etc.
The code has also been simplified.

Arrrrrr!!
(It's international Talk Like a Pirate Day today)

/*******************************************************************************
Name:		udfCharIndex2
Author:		Piquet
Creation Date:	Sep2006
Version:	3.0

Description:	Find the Nth Occurrence of a Target string within another string.
				This function can search the string from either the left or the right -
				i.e. you can find the 3rd occurrence, the 2nd to last occurrence, etc.
				If an Nth occurrence is not found, returns zero.

Parameters:		- Input(s)
			@strTarget		- The string to search for
			@strSearched	- The string being searched
			@intOccurrence	- The specific occurrence to find:
								Positive values search Left-to-Right, Negative values Right-to-Left

			- Output(s)
			Returns the character position of the @intOccurrence of @strTarget within @strSearched

Usage Example:
	SELECT dbo.udfCharIndex2('ow', 'how now brown cow', 3)
		returns the location of the third occurrence of 'ow' which is 11
	SELECT dbo.udfCharIndex2('ow', 'how now brown cow', -2)
		returns the location of the 2nd last occurrence of 'ow' which is 11
	SELECT dbo.udfCharIndex2('ow', 'how now brown cow', -1)
		returns the location of the last occurrence of 'ow' which is 16
	SELECT dbo.udfCharIndex2('ow', 'how now brown cow', 5)
		returns 0 since there are not 5 occurrences of 'ow'

Modification History:
-------------------------------------------------------------------------------
Version	Date	Name		Modification
-------------------------------------------------------------------------------
2.0		Sep2006	Vextant		http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1792
3.0		Sep2006	Piquet		Simplified search logic & added Right-to-Left searches for negative @intOccurrence values
*******************************************************************************/
CREATE FUNCTION dbo.udfCharIndex2(
	@strTarget varchar(8000), 
	@strSearched varchar(8000), 
	@intOccurrence smallint
	) RETURNS smallint AS
BEGIN
	DECLARE @intPointer smallint, @intCounter smallint

	SELECT	@intCounter = 0,
			@intPointer = 0

	-- If Right2Left search, Reverse the Target & Searched strings
	IF @intOccurrence < 0
		SELECT @strTarget = Reverse(@strTarget),
			@strSearched = Reverse(@strSearched)

	WHILE (@intCounter < ABS(@intOccurrence))
	BEGIN
		SELECT	@intPointer = CharIndex(@strTarget, @strSearched, @intPointer + 1),
				@intCounter = @intCounter + 1

		-- If Target not found, exit loop
		IF @intPointer = 0 SET @intCounter = ABS(@intOccurrence)
	END

	-- If Right2Left search, map Pointer from reversed strings back to forward strings
	IF @intOccurrence < 0 AND @intPointer <> 0 SET @intPointer = DataLength(@strSearched) - @intPointer - DataLength(@strTarget) + 2

	RETURN(@intPointer)
END

Rate

Share

Share

Rate