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