MySQL SUBSTR() function
SUBSTR() function
MySQL SUBSTR() returns the specified number of characters from a particular position of a given string. SUBSTR() is a synonym for SUBSTRING().
This function is useful in -
- Substring extraction: Substrings can be extracted by specifying their length and position in a string.
- Data manipulation: The SUBSTR() function is often used in data manipulation tasks, such as extracting specific parts of a string for further processing or modifying the content of a string based on the desired substring.
Syntax:
SUBSTR(str, pos, len)
Arguments:
Name | Description |
---|---|
str | A string from which a substring is to be returned. |
pos | An integer indicating a string position within the string str. |
len | An integer indicating a number of characters to be returned. |
The above function is a synonym for SUBSTRING().
Syntax Diagram:
MySQL Version: 8.0
The SUBSTR() function is same as SUBSTRING() function
Example: MySQL SUBSTR() function
The following MySQL statement returns 3 numbers of characters from the 4th position of the string ‘w3resource’.
Code:
SELECT SUBSTR('w3resource',4,3);
Output:
mysql> SELECT SUBSTR('w3resource',4,3); +--------------------------+ | SUBSTR('w3resource',4,3) | +--------------------------+ | eso | +--------------------------+ 1 row in set (0.01 sec)
Example of MySQL SUBSTR() using table
The following MySQL statement returns 5 numbers of characters from the 4th position of the column pub_name for those publishers which belongs to the country ‘USA’ from the table publisher.
Code:
SELECT pub_name, SUBSTR(pub_name,4,5)
FROM publisher
WHERE country='USA';
Sample table: publisher
Output:
mysql> SELECT pub_name, SUBSTR(pub_name,4,5) -> FROM publisher -> WHERE country='USA'; +--------------------------+----------------------+ | pub_name | SUBSTR(pub_name,4,5) | +--------------------------+----------------------+ | Jex Max Publication | Max | | Mountain Publication | ntain | | Summer Night Publication | mer N | +--------------------------+----------------------+ 3 rows in set (0.00 sec)
Example of MySQL SUBSTR() function extracts rest characters from a specific position
The following MySQL statement returns the rest of the characters from the 5th position of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.
Code:
SELECT pub_name, SUBSTR(pub_name,5)
FROM publisher
WHERE country='USA';
Sample table: publisher
Output:
mysql> SELECT pub_name, SUBSTR(pub_name,5) -> FROM publisher -> WHERE country='USA'; +--------------------------+----------------------+ | pub_name | SUBSTR(pub_name,5) | +--------------------------+----------------------+ | Jex Max Publication | Max Publication | | Mountain Publication | tain Publication | | Summer Night Publication | er Night Publication | +--------------------------+----------------------+ 3 rows in set (0.00 sec)
Example of MySQL SUBSTR() using FROM keyword
The following MySQL statement returns the rest of the characters from the 5th position (notice that FROM keyword is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.
Code:
SELECT pub_name,
SUBSTR(pub_name FROM 5)
FROM publisher
WHERE country='USA';
Output:
mysql> SELECT pub_name, -> SUBSTR(pub_name FROM 5) -> FROM publisher -> WHERE country='USA'; +--------------------------+-------------------------+ | pub_name | SUBSTR(pub_name FROM 5) | +--------------------------+-------------------------+ | Jex Max Publication | Max Publication | | Mountain Publication | tain Publication | | Summer Night Publication | er Night Publication | +--------------------------+-------------------------+ 3 rows in set (0.00 sec)
Example of MySQL SUBSTR() extracts from negative position
The following MySQL statement returns the rest of the characters from the 4th position from the end (since -4 is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.
Code:
SELECT pub_name, SUBSTR(pub_name ,-4)
FROM publisher
WHERE country='USA';
Sample table: publisher
Output:
mysql> SELECT pub_name, SUBSTR(pub_name ,-4) -> FROM publisher -> WHERE country='USA'; +--------------------------+----------------------+ | pub_name | SUBSTR(pub_name ,-4) | +--------------------------+----------------------+ | Jex Max Publication | tion | | Mountain Publication | tion | | Summer Night Publication | tion | +--------------------------+----------------------+ 3 rows in set (0.02 sec)
Example of MySQL SUBSTR() extracting from the end
The following MySQL statement returns 5 characters from the 15th position from the end (since -15 is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.
Code:
SELECT pub_name, SUBSTR(pub_name ,-15,5)
FROM publisher
WHERE country='USA';
Sample table: publisher
Output:
mysql> SELECT pub_name, SUBSTR(pub_name ,-15,5) -> FROM publisher -> WHERE country='USA'; +--------------------------+-------------------------+ | pub_name | SUBSTR(pub_name ,-15,5) | +--------------------------+-------------------------+ | Jex Max Publication | Max P | | Mountain Publication | ain P | | Summer Night Publication | ght P | +--------------------------+-------------------------+ 3 rows in set (0.00 sec)
Example MySQL SUBSTR() with FROM and FOR keywords
The following MySQL statement returns 5 characters (notice that FOR keyword is used here) from the 15th position (notice that FROM keyword is used here) from the end (since -15 is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.
Code:
SELECT pub_name, SUBSTR(pub_name
FROM -15 FOR 5)
FROM publisher
WHERE country='USA';
Sample table: publisher
Output:
mysql> SELECT pub_name, SUBSTR(pub_name FROM -15 FOR 5) -> FROM publisher -> WHERE country='USA';
+--------------------------+---------------------------------+ | pub_name | SUBSTR(pub_name FROM -15 FOR 5) | +--------------------------+---------------------------------+ | Jex Max Publication | Max P | | Mountain Publication | ain P | | Summer Night Publication | ght P | +--------------------------+---------------------------------+ 3 rows in set (0.00 sec)
Video Presentation:
All String Functions (Slides presentation)
Previous: STRCMP
Next: SUBSTRING_INDEX
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/mysql/string-functions/mysql-substr-function.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics