How INSTR() Works in MariaDB

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

INSTR() is similar to the LOCATE() 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 syntax goes like this:

INSTR(str,substr)

Where str is the string, substr is the substring to find.

Example

Here’s a basic example:

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

Result:

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

Case Insensitive

INSTR() performs a case insensitive search:

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

Result:

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

Multiple Occurrences

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

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

Result:

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

Partial Match

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

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

Result:

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

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

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

Result:

+---------------------------------+
| INSTR('The hot sun', 'hottest') |
+---------------------------------+
|                               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 INSTR('The hot sun', ' ');

Result:

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

Empty String

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

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

Result:

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

No Match

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

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

Result:

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

Null Arguments

Providing null for any of the arguments results in null:

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

Result:

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

Providing the Wrong Number of Arguments

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

SELECT INSTR('hot');

Result:

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