How the LOCATE() Function Works in MySQL

In MySQL, the LOCATE() function returns the position of a substring within a string. More specifically, it returns the position of the first occurrence within the string, or the first occurrence after a given starting point.

Syntax

It can be used in either of the following ways:

LOCATE(substr,str)
LOCATE(substr,str,pos)

Where substr is the substring to locate, and str is the string to search.

When using the second syntax, pos is the position to start searching.

Example 1 – First Syntax

Here’s an example using the first syntax:

SELECT LOCATE('cat', 'One cat jumped over the other cat') AS Result;

Result:

+--------+
| Result |
+--------+
|      5 |
+--------+

Example 2 – Second Syntax

Here’s an example where we specify a start position to start searching:

SELECT LOCATE('cat', 'One cat jumped over the other cat', 6) AS Result;

Result:

+--------+
| Result |
+--------+
|     31 |
+--------+

In this case, the first occurrence of cat begins at position 5, but I specified the search to start at position 6. Therefore, the position of the next occurrence of that string was the one that was returned.

Note that, although the search started at position 6, the function still returns the position of the substring within the string – not from the start position.

Here’s another example to help make this clearer.

SELECT 
  LOCATE('c', 'a b c', 1) AS 'Result 1',
  LOCATE('c', 'a b c', 2) AS 'Result 2',
  LOCATE('c', 'a b c', 4) AS 'Result 3';

Result:

+----------+----------+----------+
| Result 1 | Result 2 | Result 3 |
+----------+----------+----------+
|        5 |        5 |        5 |
+----------+----------+----------+

The result is the same no matter where we start searching.

Example 3 – Locating Part of a Word

The substring can be part of a longer word:

SELECT LOCATE('sing', 'Increasingly') AS Result;

Result:

+--------+
| Result |
+--------+
|      7 |
+--------+

In fact, there’s no requirement for it to even be a word (after all, we’re simply searching a string):

SELECT 
  LOCATE('z23!#', 'u_4, z23!#') AS 'Result 1',
  LOCATE(' ', 'a b c') AS 'Result 2',
  LOCATE(',', 'cat, dog, bird') AS 'Result 3';

Result:

+----------+----------+----------+
| Result 1 | Result 2 | Result 3 |
+----------+----------+----------+
|        6 |        2 |        4 |
+----------+----------+----------+

Example 4 – No Matches

If the substring isn’t found, 0 is returned:

SELECT LOCATE('Bat', 'Increasingly') AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 5 – Case Sensitivity

This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

Therefore the following works on nonbinary strings, even though the case doesn’t match:

SELECT LOCATE('Sing', 'Increasingly') AS Result;

Result:

+--------+
| Result |
+--------+
|      7 |
+--------+

But if we use a binary string, this happens:

SET @str = BINARY 'Increasingly'; 
SELECT LOCATE('Sing', @str) AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

But of course, if we change it so that the case matches, we get a match:

SET @str = BINARY 'Increasingly'; 
SELECT LOCATE('sing', @str) AS Result;

Result:

+--------+
| Result |
+--------+
|      7 |
+--------+

Example 6 – NULL Arguments

If any of the arguments are NULL, NULL is returned:

SELECT 
  LOCATE(NULL, 'Increasingly') a,
  LOCATE('Bat', NULL) b,
  LOCATE('Bat', 'Increasingly', NULL) c;

Result:

+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+