MariaDB has an INSTR() function and also a LOCATE() function. Both functions appear to do exactly the same thing – return the position of a substring within a string.

However, there is a difference.

The Differences

We can see the difference between these two functions by looking at their syntax, as well as a couple of examples.

Syntax

The syntax for INSTR():

INSTR(str,substr)

The two accepted syntaxes for LOCATE():

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

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

The difference between the two functions can be summarised as follows:

• The LOCATE() function accepts the same two arguments that INSTR() accepts, but in reversed order.

• The LOCATE() function accepts an optional third argument to specify the starting position for the search. The INSTR() function doesn’t accept such an argument.

So LOCATE() provides extra functionality that INSTR() doesn’t provide.

Example 1 – The First Difference

Here’s an example that shows the reverse order in which the two functions accept their arguments:

SELECT 
    INSTR('Disconnect', 'con'),
    LOCATE('con', 'Disconnect');

Result:

+----------------------------+-----------------------------+
| INSTR('Disconnect', 'con') | LOCATE('con', 'Disconnect') |
+----------------------------+-----------------------------+
|                          4 |                           4 |
+----------------------------+-----------------------------+

Example 2 – The Second Difference

Here’s an example that shows the extra functionality that LOCATE() provides over INSTR():

SELECT 
    INSTR('My cat is a good cat', 'cat') AS "INSTR()",
    LOCATE('cat', 'My cat is a good cat') AS "LOCATE()",
    LOCATE('cat', 'My cat is a good cat', 10) AS "LOCATE() with 3rd Argument";

Result:

+---------+----------+----------------------------+
| INSTR() | LOCATE() | LOCATE() with 3rd Argument |
+---------+----------+----------------------------+
|       4 |        4 |                         18 |
+---------+----------+----------------------------+

This example highlights the fact that the LOCATE() function provides us with an optional third argument. This argument allows us to specify a starting position for the search, which means we aren’t limited to just the first occurrence in the whole string.