How the POSITION() function works in Mariadb?

The POSITION() function is a string function in Mariadb that returns the position of the first occurrence of a substring within a string. The function is case-sensitive and uses the character position as the unit of measurement. The function is equivalent to the standard SQL function LOCATE().

Syntax

The syntax of the POSITION() function is as follows:

POSITION(substring IN string)

The function takes one argument:

• substring: A string that represents the substring to be searched for within the string. The substring cannot be empty or NULL.
• string: A string that represents the string to be searched within. The string cannot be NULL.

The function returns an integer that represents the position of the first occurrence of the substring within the string, starting from 1. If the substring is not found, the function returns 0.

Examples

Example 1: Finding the position of a substring

The following example finds the position of the substring 'cat' within the string 'The cat is on the mat'.

SELECT POSITION('cat' IN 'The cat is on the mat') AS position;

The output is:

+----------+
| position |
+----------+
|        5 |
+----------+

The output shows that the substring 'cat' is found at the 5th character position of the string 'The cat is on the mat'.

Example 2: Finding the position of a substring with special characters

The following example finds the position of the substring '@' within the string '[email protected]'.

SELECT POSITION('@' IN '[email protected]') AS position;

The output is:

+----------+
| position |
+----------+
|        9 |
+----------+

The output shows that the substring '@' is found at the 9th character position of the string '[email protected]'.

Example 3: Finding the position of a substring that does not exist

The following example finds the position of the substring 'dog' within the string 'The cat is on the mat'.

SELECT POSITION('dog' IN 'The cat is on the mat') AS position;

The output is:

+----------+
| position |
+----------+
|        0 |
+----------+

The output shows that the substring 'dog' is not found in the string 'The cat is on the mat', so the function returns 0.

Related Functions

There are some other functions that are related to the POSITION() function, such as:

• LOCATE(): This function is a synonym for the POSITION() function. The syntax of the function is LOCATE(substring, string, [position]), where substring and string are the same as in the POSITION() function, and position is an optional integer that specifies the starting position for the search. If position is not specified, the search starts from the beginning of the string. The function returns the same result as the POSITION() function.
• INSTR(): This function is similar to the POSITION() function, but it uses the byte position as the unit of measurement instead of the character position. The syntax of the function is INSTR(string, substring), where string and substring are the same as in the POSITION() function. The function returns the byte position of the first occurrence of the substring within the string, starting from 1. If the substring is not found, the function returns 0.

Conclusion

The POSITION() function is a useful function to find the position of the first occurrence of a substring within a string. The function is case-sensitive and uses the character position as the unit of measurement. The function is equivalent to the standard SQL function LOCATE(). The function takes one argument, which is the substring to be searched for within the string. The function returns an integer that represents the position of the first occurrence of the substring within the string, starting from 1. If the substring is not found, the function returns 0. The function can be used to perform string manipulation and analysis, such as extracting, replacing, or validating substrings. The function can also be combined with other string functions, such as SUBSTRING(), REPLACE(), REGEXP(), etc., to perform more complex operations on strings.