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'