3 Ways to Find a Substring's Position in MySQL

MySQL has three equivalent spellings for finding a substring’s position: LOCATE(), INSTR(), and POSITION(). They return a 1-based position, 0 when no match is found, and NULL if an argument is NULL. The main difference is argument order and whether you can specify a starting position. See the MySQL string function reference.

Compare the three forms

Each form below finds the first bar in foobarbar, at position 4:

SELECT
    LOCATE('bar', 'foobarbar') AS locate_result,
    INSTR('foobarbar', 'bar') AS instr_result,
    POSITION('bar' IN 'foobarbar') AS position_result;

locate_result  instr_result  position_result
-------------  ------------  ---------------
4              4             4

INSTR(str, substr) reverses the two arguments used by LOCATE(substr, str). POSITION(substr IN str) is equivalent to the two-argument LOCATE() form. Read the SQLiz references for LOCATE(), INSTR(), and POSITION().

Start searching later in the string

Only LOCATE() accepts a starting position. The result remains an absolute position in the original string:

SELECT LOCATE('bar', 'foobarbar', 5) AS next_match;

next_match
----------
7

The search begins at position 5, skips the occurrence that starts at position 4, and returns the next occurrence at position 7.

Not found, NULL, and case behavior

All three return 0 when the substring is absent. LOCATE() and INSTR() return NULL if any argument is NULL; POSITION() does too because it is equivalent to LOCATE() with two arguments.

For nonbinary strings, matching follows MySQL’s collation. A case-insensitive collation can match Bar when searching for bar; if either argument is binary, MySQL performs a case-sensitive match. Check the column’s collation if the result differs from what you expect.

These functions find literal substrings. To search by a regular expression, use REGEXP_LIKE() or the REGEXP operator instead.