How the POSITION() Function Works in MySQL

In MySQL, the POSITION() function returns the position of the first occurrence of a substring within the string.

This function is actually a synonym for LOCATE(), but with a slightly different syntax. The LOCATE() function also has extra functionality, in that it allows you to define a starting position fro your search.

Syntax

The basic syntax goes like this:

POSITION(substr IN str)

Where substr is the substring and str is the string.

Example 1 – Basic Usage

SELECT POSITION('Cat' IN 'The Cat') Result;

Result:

+--------+
| Result |
+--------+
|      5 |
+--------+

Example 2 – Case Sensitivity

The POSITION() function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

So the following example is case-insensitive:

SELECT POSITION('Cat' IN 'The cat') Result;

Result:

+--------+
| Result |
+--------+
|      5 |
+--------+

Example 3 – No Match

The function returns 0 if the substring isn’t found within the string:

SELECT POSITION('Dog' IN 'The cat') Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 4 – Embedded Substrings

Be careful when choosing the substring. If it’s part of another word, you could get a false positive:

SELECT POSITION('Cat' IN 'Good Catch!') Result;

Result:

+--------+
| Result |
+--------+
|      6 |
+--------+

If you’re only looking for cat, the animal, this would be a false positive.

Example 5 – First Occurrence Only

Remember, this function only returns the position of the first occurrence of the substring. Any subsequent occurrences are ignored:

SELECT POSITION('Cat' IN 'My cat is bigger than your cat') Result;

Result:

+--------+
| Result |
+--------+
|      4 |
+--------+

Example 6 – NULL Arguments

If any of the arguments are NULL, the result is NULL:

SELECT 
  POSITION('Cat' IN NULL) 'Result 1',
  POSITION(NULL IN 'The Cat') 'Result 2';

Result:

+----------+----------+
| Result 1 | Result 2 |
+----------+----------+
|     NULL |     NULL |
+----------+----------+

As mentioned, you can also use the LOCATE() function, which allows you define a starting position for your search.