Menu

MySQL POSITION() Syntax and Examples

Updated on

MySQL POSITION(substr IN str) returns the position of the first occurrence of substr in str. This SQL-standard spelling is equivalent to the two-argument form of LOCATE(substr, str). See the MySQL string function reference.

Syntax

POSITION(substr IN str)

Unlike LOCATE(), POSITION() does not accept a starting-position argument. The result is 1-based, 0 if the substring is absent, and NULL if either value is NULL:

SELECT
    POSITION('bar' IN 'foobarbar') AS found_at,
    POSITION('baz' IN 'foobar') AS not_found,
    POSITION(NULL IN 'foobar') AS null_substring;

found_at  not_found  null_substring
--------  ---------  --------------
4         0          NULL

For a third pos argument, use LOCATE(substr, str, pos). For the reverse argument order, use INSTR(str, substr).