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:
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook