MySQL function to find the nth occurrence of a substring in a string.

locate_offset

# Drop the function if it's already there
drop function if exists LOCATE_OFFSET;

# Define the function
# @param substr The substring to search for
# @param str The string to search through
# @param offset The nth-occurrence being looked for (1-indexed, using 0 will always return 0)
DELIMITER //
CREATE FUNCTION LOCATE_OFFSET(substr text, str text, offset int)
RETURNS int
DETERMINISTIC
BEGIN
DECLARE loc INT DEFAULT 1;
DECLARE i INT DEFAULT 0;
WHILE(i<offset) DO
SET loc=LOCATE(substr, str, loc+1);
SET i=i+1;
END WHILE;
RETURN(loc);
END//
DELIMITER ;

# Example
# Returns 7
SELECT LOCATE_OFFSET( 'bar', 'foobarbar', 2 );

Thank you for sharing this stored function.

Changing lines 15 - 18 to:

WHILE(i < offset AND loc IS NOT NULL) DO
  SET loc = NULLIF(LOCATE(substr, str, loc + 1), 0);
  SET i = i + 1;
END WHILE;

will make this function return NULL instead of strange values, when attempting to return an occurrence that does not exist.