MySQL POSITION() Function
The MySQL POSITION() function returns the location of the first occurrence of a specified substring in a given string. If the substring is not found within the given string, this function returns 0.
This function performs a case-insensitive search. Note that the first position in string starts with 1.
The POSITION() function is a synonym for the LOCATE() function.
Syntax
POSITION(substring, string, start_position)
Parameters
substring |
Required. Specify the substring to search for in string. |
string |
Required. Specify the string to search. |
start_position |
Optional. Specify the starting position for the search. Default is position 1. |
Return Value
Returns the location of the first occurrence of the substring in the string.
Example 1:
The example below shows the usage of POSITION() function.
mysql> SELECT POSITION('A', 'AlphaCodingSkills.com'); Result: 1 mysql> SELECT POSITION('Coding', 'AlphaCodingSkills.com'); Result: 6 mysql> SELECT POSITION('.com', 'AlphaCodingSkills.com'); Result: 18 mysql> SELECT POSITION('l', 'AlphaCodingSkills.com'); Result: 2 mysql> SELECT POSITION('l', 'AlphaCodingSkills.com', 5); Result: 15 mysql> SELECT POSITION('Z', 'AlphaCodingSkills.com'); Result: 0 mysql> SELECT POSITION('ABC', 'AlphaCodingSkills.com'); Result: 0
Example 2:
Consider a database table called Employee with the following records:
PhoneNumber | EmpID | Address |
---|---|---|
+1-8054098000 | 1 | Brooklyn, New York, USA |
+33-147996101 | 2 | Grenelle, Paris, France |
+31-201150319 | 3 | Geuzenveld, Amsterdam, Netherlands |
+86-1099732458 | 4 | Yizhuangzhen, Beijing, China |
+65-67234824 | 5 | Yishun, Singapore |
+81-357799072 | 6 | Koto City, Tokyo, Japan |
In the query below, the POSITION() function is used to extract the country code from the PhoneNumber column records.
SELECT *, SUBSTR(PhoneNumber, 1, POSITION('-', PhoneNumber) - 1) AS CountryCode FROM Employee;
This will produce the result as shown below:
PhoneNumber | EmpID | Address | CountryCode |
---|---|---|---|
+1-8054098000 | 1 | Brooklyn, New York, USA | +1 |
+33-147996101 | 2 | Grenelle, Paris, France | +33 |
+31-201150319 | 3 | Geuzenveld, Amsterdam, Netherlands | +31 |
+86-1099732458 | 4 | Yizhuangzhen, Beijing, China | +86 |
+65-67234824 | 5 | Yishun, Singapore | +65 |
+81-357799072 | 6 | Koto City, Tokyo, Japan | +81 |
❮ MySQL Functions