MySQL in a Nutshell

Name

LOCATE( )

Synopsis

LOCATE(string, string[, start_position])

This function returns the numeric starting point of the first occurrence of a string given in the first argument, of the function found in the string given in the second argument. MySQL does not search beyond this point. A starting position for searching may be specified as a third argument.

SELECT name_last AS 'Name',
LOCATE('n', name_last) AS 'First n',
LOCATE('n', name_last, 5) AS 'After 5th Char'
FROM teachers
WHERE teacher_id = '730522';
+--------+---------+----------------+
| Name   | First n | After 5th Char |
+--------+---------+----------------+
| Vernon |       4 |              6 |
+--------+---------+----------------+

In this SQL statement, the last name of the teacher selected contains the letter “n” twice. However, the first use of the LOCATE( ) function makes note only of the position of the first occurrence. The second use of LOCATE( ) gives a starting point for MySQL to search name_last that is beyond the first occurrence, so the position of the next one is returned.