How LOCATE() Works in MariaDB

In MariaDB, LOCATE() is a built-in string function that returns the position of the first occurrence of a substring within another string.

LOCATE() is similar to the INSTR() function, except that LOCATE() provides the option of specifying a starting position for the search. Also, the argument order is reversed between these two functions.

Syntax

The LOCATE() function supports a two-argument syntax, and a three-argument syntax.

Two argument syntax:

LOCATE(substr,str)

Three argument syntax:

LOCATE(substr,str,pos)

Where str is the string, substr is the substring to find, and pos is the position to start the search.

Example

Here’s a basic example:

SELECT LOCATE('hot', 'The hot sun');

Result:

+------------------------------+
| LOCATE('hot', 'The hot sun') |
+------------------------------+
|                            5 |
+------------------------------+

Starting Position

Here’s an example of providing a starting position for the search:

SELECT LOCATE('hot', 'The hot sun is really hot', 8);

Result:

+------------------------------------------------+
| LOCATE('hot', 'The hot sun is really hot', 8)  |
+------------------------------------------------+
|                                             23 |
+------------------------------------------------+

Case Sensitivity

LOCATE() performs a case insensitive search:

SELECT LOCATE('HOT', 'The hot sun');

Result:

+------------------------------+
| LOCATE('HOT', 'The hot sun') |
+------------------------------+
|                            5 |
+------------------------------+

Multiple Occurrences

If the substring occurs more than once, only the position of the first one (from the starting position) is returned:

SELECT LOCATE('hot', 'The hot sun is really hot');

Result:

+--------------------------------------------+
| LOCATE('hot', 'The hot sun is really hot') |
+--------------------------------------------+
|                                          5 |
+--------------------------------------------+

Here it is with a starting position:

SELECT LOCATE('hot', 'The hot sun is really hot', 20);

Result:

+------------------------------------------------+
| LOCATE('hot', 'The hot sun is really hot', 20) |
+------------------------------------------------+
|                                             23 |
+------------------------------------------------+

Partial Match

If the substring is part of a larger word, it’s still a match:

SELECT LOCATE('hot', 'The hottest sun');

Result:

+----------------------------------+
| LOCATE('hot', 'The hottest sun') |
+----------------------------------+
|                                5 |
+----------------------------------+

But if the string is only part of the substring, it’s not a match:

SELECT LOCATE('hottest', 'The hot sun');

Result:

+----------------------------------+
| LOCATE('hottest', 'The hot sun') |
+----------------------------------+
|                                0 |
+----------------------------------+

Spaces

The space character is still a string, so we can include spaces in the substring, even if it’s the only character:

SELECT LOCATE(' ', 'The hot sun');

Result:

+----------------------------+
| LOCATE(' ', 'The hot sun') |
+----------------------------+
|                          4 |
+----------------------------+

Empty String

Here’s what happens when the substring is an empty string:

SELECT LOCATE('', 'The hot sun');

Result:

+---------------------------+
| LOCATE('', 'The hot sun') |
+---------------------------+
|                         1 |
+---------------------------+

No Match

If the substring isn’t found in the string, LOCATE() returns 0:

SELECT LOCATE('cold', 'The hot sun');

Result:

+-------------------------------+
| LOCATE('cold', 'The hot sun') |
+-------------------------------+
|                             0 |
+-------------------------------+

Null Arguments

Providing null for any of the arguments results in null:

SELECT 
    LOCATE(null, 'The hot sun'),
    LOCATE('hot', null);

Result:

+-----------------------------+---------------------+
| LOCATE(null, 'The hot sun') | LOCATE('hot', null) |
+-----------------------------+---------------------+
|                        NULL |                NULL |
+-----------------------------+---------------------+

Providing the Wrong Number of Arguments

Calling LOCATE() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT LOCATE('hot');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LOCATE'