The INSTR() function returns the position of the first occurrence of a substring within a string. Basically, it does the same thing that the two-argument syntax of the LOCATE() function does (except that the order of the arguments is reversed).
Syntax
The syntax goes like this:
INSTR(str,substr)
Where str is the string you want to search, and substr is the substring that you’re searching for.
Example 1 – Basic Usage
SELECT INSTR('Cats and dogs like to run', 'dogs') AS Result;
Result:
+--------+ | Result | +--------+ | 10 | +--------+
In this case, the string dogs starts at position 10.
Example 2 – Not Case Sensitive
It’s not case sensitive:
SELECT
INSTR('Cats and dogs like to run', 'DOGS') AS 'Result 1',
INSTR('Cats and DOGS like to run', 'dogs') AS 'Result 2';
Result:
+----------+----------+ | Result 1 | Result 2 | +----------+----------+ | 10 | 10 | +----------+----------+
Example 3 – Partial Match
It doesn’t matter if your search term only represents part of a word, it will still match (after all, you’re simply searching for a substring within a string):
SELECT INSTR('Cats and dogs like to run', 'do') AS Result;
Result:
+--------+ | Result | +--------+ | 10 | +--------+
A space is still part of the string. So you can search for the first space if you need to:
SELECT INSTR('Cats and dogs like to run', ' ') AS Result;
Result:
+--------+ | Result | +--------+ | 5 | +--------+
Example 4 – First Occurrence Only
Remember, only the position of the first occurrence is returned:
SELECT INSTR('Cats and dogs like to run', 'a') AS Result;
Result:
+--------+ | Result | +--------+ | 2 | +--------+
Example 5 – When the Substring Can’t be Found
If the substring isn’t found, a result of 0 is returned:
SELECT INSTR('Cats and dogs like to run', 'rabbit') AS Result;
Result:
+--------+ | Result | +--------+ | 0 | +--------+
Example 6 – Incorrect Parameter Count
If you omit any of the parameters, you’ll get an error:
SELECT INSTR('Cats and dogs like to run') AS Result;
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'INSTR'
You’ll also get an error if you provide too many parameters:
SELECT INSTR('Cats and dogs like to run', 'dogs', 'cats') AS Result;
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'INSTR'