MySQL SUBSTR() function

Last update on July 19 2023 13:16:25 (UTC/GMT +8 hours)

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