MySQL has a number of string functions that return the position of a substring within a string. More specifically, they return the position of the first occurrence within the string (or the first occurrence after a given starting point).

The functions I’m referring to are as follows:

• INSTR()
• LOCATE()
• POSITION()

Below is an overview of each one.

INSTR()

The INSTR() function returns the first occurrence of a substring within a string.

Here’s the syntax:

INSTR(str,substr)

Where str is the string you want to search, and substr is the substring that you’re searching for.

Example

SELECT INSTR('Cats and dogs like to run', 'dogs') AS Result;

Result:

+--------+
| Result |
+--------+
|     10 |
+--------+

In this case, the string dogs starts at position 10.

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 |
+--------+

LOCATE()

The LOCATE() function works in a similar way, except for a couple of differences; the arguments are in a different order, and you can provide an optional third parameter to indicate where to start searching.

Therefore, you can use it in one of the following ways:

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

The first syntax is just like the INTSR() syntax, except that str and substr are swapped around.

The second syntax adds the optional pos argument, which allows you to specify a position to start searching.

Example of First Syntax

Here’s an example using the first syntax:

SELECT LOCATE('dogs', 'Cats and dogs like to run') AS Result;

Result:

+--------+
| Result |
+--------+
|     10 |
+--------+

Example of Second Syntax

Here’s an example where we specify a start position to start searching:

SELECT LOCATE('cat', 'Cats, cats, and more cats!', 6) AS Result;

Result:

+--------+
| Result |
+--------+
|      7 |
+--------+

We can see that the string cat can be found at position 1, but we specified 6 as the starting position. Therefore, the position of the next occurrence of that string was the one that was returned.

As with the INSTR() function, if the substring isn’t found, 0 is returned.

POSITION()

The POSITION() function is a synonym for LOCATE(), but with a slightly different syntax.

Here’s the syntax:

POSITION(substr IN str)

This is a synonym for the following:

LOCATE(substr,str)

So, we could rewrite one of the previous examples as follows:

SELECT POSITION('dogs' IN 'Cats and dogs like to run') AS Result;

Result:

+--------+
| Result |
+--------+
|     10 |
+--------+