Menu

MySQL LOCATE() Function: Syntax and Examples

Updated on

MySQL LOCATE() returns the position of the first occurrence of a substring. Its first argument is the substring to find, followed by the string to search. See the MySQL string function reference.

Syntax

LOCATE(substr, str)
LOCATE(substr, str, pos)

pos is an optional 1-based position where searching begins. The returned position is still measured from the start of str:

SELECT
    LOCATE('bar', 'foobarbar') AS first_match,
    LOCATE('bar', 'foobarbar', 5) AS next_match,
    LOCATE('baz', 'foobar') AS not_found;

first_match  next_match  not_found
-----------  ----------  ---------
4            7           0

The result is 0 when substr is absent and NULL if any argument is NULL. This function is multibyte-safe. For nonbinary strings, matching follows the collation; if either argument is binary, matching is case-sensitive.