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:

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:

All String Functions (Slides presentation)

Previous: ORD
Next: QUOTE