MySQL SUBSTRING() function

SUBSTRING() function

MySQL SUBSTRING() returns a specified number of characters from a particular position of a given string.

This function is useful in -

• Substring extraction: We can extract a portion of a string by specifying its starting position and length.
• Data manipulation: The SUBSTRING() 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:

SUBSTRING(str, pos, len)

OR

SUBSTRING(str FROM pos FOR len)

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example : MySQL SUBSTRING() function

The following MySQL statement returns 3 numbers of characters from the 4th position of the string ‘w3resource’.

Code:

SELECT SUBSTRING('w3resource',4,3); 

Output:

mysql> SELECT SUBSTRING('w3resource',4,3);
+-----------------------------+
| SUBSTRING('w3resource',4,3) |
+-----------------------------+
| eso                         | 
+-----------------------------+
1 row in set (0.00 sec)

Example MySQL SUBSTRING() function using table

The following MySQL statement returns 5 numbers of characters from the 4th position of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Code:

SELECT pub_name, SUBSTRING(pub_name,4,5) 
FROM publisher 
WHERE country='USA';

Sample table: publisher

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name,4,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+-------------------------+
| pub_name                 | SUBSTRING(pub_name,4,5) |
+--------------------------+-------------------------+
| Jex Max Publication      |  Max                    | 
| Mountain Publication     | ntain                   | 
| Summer Night Publication | mer N                   | 
+--------------------------+-------------------------+
3 rows in set (0.00 sec)

The following MySQL statement returns the remaining 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, SUBSTRING(pub_name,5) 
FROM publisher 
WHERE country='USA';

Sample table: publisher

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+-----------------------+
| pub_name                 | SUBSTRING(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 MySQL SUBSTRING() function with FROM keyword

The following MySQL statement returns the remaining 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, SUBSTRING(pub_name FROM 5) 
FROM publisher 
WHERE country='USA';

Sample table: publisher

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name FROM 5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------------+
| pub_name                 | SUBSTRING(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 SUBSTRING() to extracts from a negative position

The following MySQL statement returns the remaining characters from the 4th position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case the value of position of the beginning of the retrieval is negative. So the beginning of the retrieval is 4 characters from the end of the string rather than the beginning.

Code:

SELECT pub_name, SUBSTRING(pub_name ,-4)
FROM publisher 
WHERE country='USA';

Sample table: publisher

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name ,-4) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------------+
| pub_name                 | SUBSTRING(pub_name , -4)   |
+--------------------------+----------------------------+
| Jex Max Publication      | tion                       | 
| Mountain Publication     | tion                       | 
| Summer Night Publication | tion                       | 
+--------------------------+----------------------------+
3 rows in set (0.00 sec)		  

Example of MySQL SUBSTRING() function extracts from the end

The following MySQL statement returns the 5 number of characters from the 15th position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case the value of position of the beginning of the retrieval is negative. So the beginning of the retrieval is 15 characters from the end of the string rather than the beginning.

Code:

SELECT pub_name, SUBSTRING(pub_name ,-15,5) 
FROM publisher 
WHERE country='USA';

Sample table: publisher

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name ,-15,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------------+
| pub_name                 | SUBSTRING(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 of MySQL SUBSTRING() using FROM and FOR keywords

The following MySQL statement returns the 5 (Notice that FOR keyword is used) numbers of characters from the 15th (Notice that FROM keyword is used) position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case, the value of the position of the beginning of the retrieval is negative. So the beginning of the retrieval is 15 characters from the end of the string rather than the beginning.

Code:

SELECT pub_name, 
SUBSTRING(pub_name FROM -15 FOR 5) 
FROM publisher 
WHERE country='USA';

Sample table: publisher

Output:

mysql> SELECT pub_name, 
    -> SUBSTRING(pub_name FROM -15 FOR 5) 
    -> FROM publisher 
    -> WHERE country='USA'; 
+--------------------------+------------------------------------+
| pub_name                 | SUBSTRING(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: SUBSTRING_INDEX
Next: TRIM