MariaDB POSITION() Function

In MariaDB, POSITION() is a built-in string function that returns the position of the first occurrence of a substring within a string.

The POSITION() function performs a case-insensitive search.

POSITION() is similar to INSTR() and LOCATE().

MariaDB POSITION() Syntax

Here is the syntax of the MariaDB POSITION() function:

POSITION(substr IN str)

Note that there is a IN keyword , which is different from LOCATE(substr, str) and INSTR(str, substr).

Parameters

substr

Required. The substring to search for in str.

str

Required. The string to be searched for.

If you provide no parameters or provide the wrong parameters, MariaDB will report an error: 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.

Return value

The MariaDB POSITION() function returns the position index of the substring substr within the string str. The indexe starts at 1. If not found substr in str, the POSITION() function will return 0.

If the substring argument is an empty string, the LOCATE() function will return 1.

If any of the arguments is NULL, the POSITION() function will return NULL.

MariaDB POSITION() Examples

Basic usage

Here’s a basic example:

SELECT POSITION('World' IN 'Hello World');

Output:

+------------------------------------+
| POSITION('World' IN 'Hello World') |
+------------------------------------+
|                                  7 |
+------------------------------------+

MariaDB POSITION() performs a case-insensitive search:

SELECT
  POSITION('world' IN 'Hello World'),
  POSITION('WORLD' IN 'Hello World');

Output:

+------------------------------------+------------------------------------+
| POSITION('world' IN 'Hello World') | POSITION('WORLD' IN 'Hello World') |
+------------------------------------+------------------------------------+
|                                  7 |                                  7 |
+------------------------------------+------------------------------------+

The first occurrence

The POSITION() only returns the first occurrence of the substring:

SELECT POSITION('o' IN 'Hello World');

Output:

+--------------------------------+
| POSITION('o' IN 'Hello World') |
+--------------------------------+
|                              5 |
+--------------------------------+

Empty string

If substring is an empty string, the POSITION() function will return 1.

SELECT POSITION('' IN 'Hello World');

Output:

+-------------------------------+
| POSITION('' IN 'Hello World') |
+-------------------------------+
|                             1 |
+-------------------------------+

Conclusion

In MariaDB, POSITION() is a built-in string function that returns the position of the first occurrence of a substring within a string.