MySQL SUBSTR() Function
The MySQL SUBSTR() function is used to extract a substring from a string starting from specified position.
The SUBSTR() and MID() functions are synonyms of the SUBSTRING() function.
Syntax
/* version 1 */ SUBSTR(string, start, length) /* version 2 */ SUBSTR(string FROM start FOR length)
Parameters
string |
Required. Specify the string to extract from. |
start |
Required. Specify the start position. It can be both a positive or negative number.
|
length |
Optional. Specify the number of characters to extract. If omitted, the whole string will be returned (from the start position). |
Return Value
Returns the substring extracted from specified string.
Example 1:
The example below shows the usage of SUBSTR() function.
mysql> SELECT SUBSTR('AlphaCodingSkills.com', 1);
Result: 'AlphaCodingSkills.com'
mysql> SELECT SUBSTR('AlphaCodingSkills.com' FROM 1);
Result: 'AlphaCodingSkills.com'
mysql> SELECT SUBSTR('AlphaCodingSkills.com', 6);
Result: 'CodingSkills.com'
mysql> SELECT SUBSTR('AlphaCodingSkills.com' FROM 6);
Result: 'CodingSkills.com'
mysql> SELECT SUBSTR('AlphaCodingSkills.com', 6, 6);
Result: 'Coding'
mysql> SELECT SUBSTR('AlphaCodingSkills.com' FROM 6 FOR 6);
Result: 'Coding'
mysql> SELECT SUBSTR('AlphaCodingSkills.com', -4, 4);
Result: '.com'
mysql> SELECT SUBSTR('AlphaCodingSkills.com' FROM -4 FOR 4);
Result: '.com'
Example 2:
Consider a database table called Employee with the following records:
| PhoneNumber | EmpID | Address |
|---|---|---|
| +33-147996101 | 1 | Grenelle, Paris, France |
| +31-201150319 | 2 | Geuzenveld, Amsterdam, Netherlands |
| +86-1099732458 | 3 | Yizhuangzhen, Beijing, China |
| +65-67234824 | 4 | Yishun, Singapore |
| +81-357799072 | 5 | Koto City, Tokyo, Japan |
In the query below, the SUBSTR() function is used to extract the country code from the PhoneNumber column records.
SELECT *, SUBSTR(PhoneNumber, 2, 2) AS CountryCode FROM Employee;
This will produce the result as shown below:
| PhoneNumber | EmpID | Address | CountryCode |
|---|---|---|---|
| +33-147996101 | 1 | Grenelle, Paris, France | 33 |
| +31-201150319 | 2 | Geuzenveld, Amsterdam, Netherlands | 31 |
| +86-1099732458 | 3 | Yizhuangzhen, Beijing, China | 86 |
| +65-67234824 | 4 | Yishun, Singapore | 65 |
| +81-357799072 | 5 | Koto City, Tokyo, Japan | 81 |
❮ MySQL Functions
