MariaDB LOCATE() Function

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

MariaDB LOCATE() performs a case-insensitive searche. There are functions similar to it: INSTR() and POSITION().

MariaDB LOCATE() Syntax

Here is the syntax of the MariaDB LOCATE() function:

LOCATE(substr, str[, startPos])

Parameters

substr

Required. The substring to search for in str.

str

Required. The string to be searched for.

startPos

Optional. The position in str to start the search, starting at 1.

If you provide the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LOCATE'.

Return value

The MariaDB LOCATE() function returns the positional index of the substr substring within the str string. Numeric indexes start at 1. If not found substr in str, the LOCATE() function will return 0.

If startPos is 0, the LOCATE() function will return 0.

If the substring argument is an empty string, the LOCATE() function will return 1.

If any of the arguments is NULL, the LOCATE() function will return NULL.

MariaDB LOCATE() Examples

Basic example

The following statement shows the basic usage of the MariaDB LOCATE() function:

SELECT
  LOCATE('l', 'Hello World'),
  LOCATE('l', 'Hello World', 5);

Output:

+----------------------------+-------------------------------+
| LOCATE('l', 'Hello World') | LOCATE('l', 'Hello World', 5) |
+----------------------------+-------------------------------+
|                          3 |                            10 |
+----------------------------+-------------------------------+

In this example, the starting search position is specified to be 5, so LOCATE('l', 'Hello World', 5) returns 10.

Case insensitive

The MariaDB LOCATE() function performs case-insensitive searches, the following statement says this:

SELECT
  LOCATE('He', 'Hello World'),
  LOCATE('he', 'Hello World'),
  LOCATE('HE', 'Hello World')\G

Output:

LOCATE('He', 'Hello World'): 1
LOCATE('he', 'Hello World'): 1
LOCATE('HE', 'Hello World'): 1

Empty string

If substring is an empty string, the LOCATE() function will return 1.

SELECT LOCATE('', 'Hello World');

Output:

+---------------------------+
| LOCATE('', 'Hello World') |
+---------------------------+
|                         1 |
+---------------------------+

Start position is 0

The MariaDB LOCATE() function will return 0 if the starting position is 0.

SELECT LOCATE('He', 'Hello World', 0);

Output:

+--------------------------------+
| LOCATE('He', 'Hello World', 0) |
+--------------------------------+
|                              0 |
+--------------------------------+

Conclusion

The MariaDB LOCATE() function returns the index of the first occurrence of a substring within a string.