How the INSTR() Function Works in MySQL

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'