MySQL POSITION() function
POSITION() function
MySQL POSITION() returns the position of a substring within a string.
This function is useful in -
- String reversal: It allows us to reverse the order of characters in a string.
- Text transformation: REVERSE() can transform text data in various ways. For example, you can use it to reverse the order of words in a sentence or characters in a word.
Syntax:
POSITION(substr IN str)
Arguments:
| Name | Description |
|---|---|
| substr | A string whose position within another string (str) is to be retrieved. |
| IN | Keyword. |
| str | A string within which the position of the substring (substr) is to be retrieved. |
The above function is a synonym for LOCATE(substr,str).
MySQL Version: 8.0
Pictorial Presentation:
Example: MySQL POSITION() function
The following MySQL statement returns the position of the substring ‘ou’ within the string ‘w3resource’
Code:
SELECT POSITION("ou" IN "w3resource");
Output:
mysql> SELECT POSITION("ou" IN "w3resource");
+--------------------------------+
| POSITION("ou" IN "w3resource") |
+--------------------------------+
| 6 |
+--------------------------------+
1 row in set (0.01 sec)
Finding rows where a substring is not present in the string
The following MySQL statement returns all rows from the author table where the substring 'William' is not present in the aut_name column.
Code:
SELECT * FROM author where NOT POSITION('William' IN aut_name) > 0;
Sample table: author
Output:
aut_id|aut_name |country |home_city | ------+--------------------+---------+--------------+ AUT004|S.B.Swaminathan |India |Bangalore | AUT005|Thomas Morgan |Germany |Arnsberg | AUT006|Thomas Merton |USA |New York | AUT007|Piers Gibson |UK |London | AUT008|Nikolai Dewey |USA |Atlanta | AUT009|Marquis de Ellis |Brazil |Rio De Janerio| AUT010|Joseph Milton |USA |Houston | AUT011|John Betjeman Hunter|Australia|Sydney | AUT012|Evan Hayek |Canada |Vancouver | AUT013|E. Howard |Australia|Adelaide | AUT014|C. J. Wilde |UK |London | AUT015|Butler Andre |USA |Florida |
Finding rows where a specific character is not at the beginning of the string
The following MySQL statement returns all rows from the author table where the character 'U' is not at the beginning of the country.
Code:
SELECT * FROM author WHERE NOT POSITION('U' IN country) = 1;
Sample table: author
Output:
aut_id|aut_name |country |home_city | ------+--------------------+---------+--------------+ AUT002|William Maugham |Canada |Toronto | AUT004|S.B.Swaminathan |India |Bangalore | AUT005|Thomas Morgan |Germany |Arnsberg | AUT009|Marquis de Ellis |Brazil |Rio De Janerio| AUT011|John Betjeman Hunter|Australia|Sydney | AUT012|Evan Hayek |Canada |Vancouver | AUT013|E. Howard |Australia|Adelaide |
Finding rows where a specific substring is not present at a specific position
The following MySQL statement returns all rows from the author table where the substring 'M' is not present at the 7th position in the aut_name.
Code:
SELECT *
FROM author
WHERE not POSITION('M' IN aut_name) = 7;
Sample table: author
Output:
aut_id|aut_name |country |home_city | ------+--------------------+---------+--------------+ AUT004|S.B.Swaminathan |India |Bangalore | AUT005|Thomas Morgan |Germany |Arnsberg | AUT006|Thomas Merton |USA |New York | AUT007|Piers Gibson |UK |London | AUT008|Nikolai Dewey |USA |Atlanta | AUT009|Marquis de Ellis |Brazil |Rio De Janerio| AUT010|Joseph Milton |USA |Houston | AUT011|John Betjeman Hunter|Australia|Sydney | AUT012|Evan Hayek |Canada |Vancouver | AUT013|E. Howard |Australia|Adelaide | AUT014|C. J. Wilde |UK |London | AUT015|Butler Andre |USA |Florida |
Combining POSITION() with other conditions using AND and OR
The following MySQL statement returns all rows from the author table where both the substring 'M' in the aut_name and 'U' in the country do not meet the specified conditions.
Code:
SELECT *
FROM author
WHERE not (POSITION('M' IN aut_name) > 0 AND POSITION('U' IN country) > 0);
Sample table: author
Output:
aut_id|aut_name |country |home_city | ------+----------------+---------+--------------+ AUT002|William Maugham |Canada |Toronto | AUT004|S.B.Swaminathan |India |Bangalore | AUT005|Thomas Morgan |Germany |Arnsberg | AUT007|Piers Gibson |UK |London | AUT008|Nikolai Dewey |USA |Atlanta | AUT009|Marquis de Ellis|Brazil |Rio De Janerio| AUT012|Evan Hayek |Canada |Vancouver | AUT013|E. Howard |Australia|Adelaide | AUT014|C. J. Wilde |UK |London | AUT015|Butler Andre |USA |Florida |
Video Presentation: