MySQL POSITION() Function

In MySQL, the POSITION() function returns the index of the first occurrence of a substring in a string.

The function is the same as LOCATE() and INSTR() with two parameters.

POSITION() Syntax

Here is the syntax of MySQL POSITION() function:

POSITION(substr, str)

or

POSITION(substr, str, startPos)

Parameters

substr

Required. A substring to search for in str.

str

Required. The string that be searched.

startPos

Optional. str The position to start the search.

Return value

The POSITION() function returns the index of the first occurrence of a substring in a string. If str doses not contains substr, the POSITION() function will return 0.

• If startPos is 0, the function will return 0.
• The function will return NULL if either parameter is NULL.

POSITION() Examples

Here are some examples of MySQL POSITION() function.

SELECT
    POSITION('He', 'Hello World'),
    POSITION('he', 'Hello World'),
    POSITION('wo', 'Hello World'),
    POSITION('go', 'Hello World'),
    POSITION(NULL, 'Hello World'),
    POSITION('l', 'Hello World'),
    POSITION('l', 'Hello World', 0),
    POSITION('l', 'Hello World', 1),
    POSITION('l', 'Hello World', 6)\G

  POSITION('He', 'Hello World'): 1
  POSITION('he', 'Hello World'): 1
  POSITION('wo', 'Hello World'): 7
  POSITION('go', 'Hello World'): 0
  POSITION(NULL, 'Hello World'): NULL
   POSITION('l', 'Hello World'): 3
POSITION('l', 'Hello World', 0): 0
POSITION('l', 'Hello World', 1): 3
POSITION('l', 'Hello World', 6): 10

Here, POSITION('l', 'Hello World', 0) returns 0 because the start position is 0.