How POSITION() Works in MariaDB

In MariaDB, POSITION() is a built-in string function that returns the position of the first occurrence of a substring within another string. It’s part of ODBC 3.0.

POSITION(substr IN str) is a synonym for LOCATE(substr,str).

POSITION() is also similar to the INSTR() function, except that POSITION() uses the IN syntax, and that the argument order is reversed between these two functions.

Syntax

The syntax goes like this:

POSITION(substr IN str)

Where str is the string, and substr is the substring for which to return the position of.

Example

Here’s a basic example:

SELECT POSITION('cute' IN 'My cute dog');

Result:

+-----------------------------------+
| POSITION('cute' IN 'My cute dog') |
+-----------------------------------+
|                                 4 |
+-----------------------------------+

Case Sensitivity

POSITION() performs a case insensitive search:

SELECT POSITION('CUTE' IN 'My cute dog');

Result:

+-----------------------------------+
| POSITION('CUTE' IN 'My cute dog') |
+-----------------------------------+
|                                 4 |
+-----------------------------------+

Multiple Occurrences

If the substring occurs more than once, only the position of the first one (from the starting position) is returned:

SELECT POSITION('cute' IN 'My cute dog is SOOOO cute!');

Result:

+--------------------------------------------------+
| POSITION('cute' IN 'My cute dog is SOOOO cute!') |
+--------------------------------------------------+
|                                                4 |
+--------------------------------------------------+

Partial Match

If the substring is part of a larger word, it’s still a match:

SELECT POSITION('dog' IN 'My doggy is SOOO cute!');

Result:

+---------------------------------------------+
| POSITION('dog' IN 'My doggy is SOOO cute!') |
+---------------------------------------------+
|                                           4 |
+---------------------------------------------+

But if the string is only part of the substring, it’s not a match:

SELECT POSITION('doggy' IN 'My dog is SOOO cute!');

Result:

+---------------------------------------------+
| POSITION('doggy' IN 'My dog is SOOO cute!') |
+---------------------------------------------+
|                                           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 POSITION(' ' IN 'My dog');

Result:

+---------------------------+
| POSITION(' ' IN 'My dog') |
+---------------------------+
|                         3 |
+---------------------------+

Empty String

Here’s what happens when the substring is an empty string:

SELECT POSITION('' IN 'My dog');

Result:

+--------------------------+
| POSITION('' IN 'My dog') |
+--------------------------+
|                        1 |
+--------------------------+

No Match

If the substring isn’t found in the string, POSITION() returns 0:

SELECT position('cat' IN 'My dog');

Result:

+-----------------------------+
| position('cat' IN 'My dog') |
+-----------------------------+
|                           0 |
+-----------------------------+

Null Arguments

Providing null for any of the arguments results in null:

SELECT 
    POSITION(null IN 'My dog'),
    POSITION('dog' IN null);

Result:

+----------------------------+-------------------------+
| POSITION(null IN 'My dog') | POSITION('dog' IN null) |
+----------------------------+-------------------------+
|                       NULL |                    NULL |
+----------------------------+-------------------------+

Providing the Wrong Number of Arguments

Calling POSITION() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT POSITION('dog');

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1