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'