MySQL LOCATE() Function

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

The LOCATE() function are case-insensitive. The function is the same as POSITION() and INSTR() with two parameters.

LOCATE() Syntax

Here is the syntax of MySQL LOCATE() function:

LOCATE(substr, str)

or

LOCATE(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 LOCATE() function returns the index of the first occurrence of a substring in a string. If str doses not contains substr, the LOCATE() function will return 0.

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

LOCATE() Examples

Here are some examples of MySQL LOCATE() function.

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

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

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