MySQL Substring Function | Extract Substring from String

MySQL SUBSTRING is an inbuilt string Function in MySQL. It is used to extract a substring from a string. We will discuss the MySQL SUBSTRING Function in this article. Also, we will discuss a few examples to demonstrate it’s usage.

Syntax

SUBSTRING(string, start, length)

Parameters

The MySQL Substring Function need three parameters. Two parameters are mandatory and the third one is optional. The description of the parameters is as follows:

• string: The first parameter is the string to extract from. It is a mandatory parameter.
• start: The second parameter is the starting point of extraction. If the parameter is positive then substring Function extracts from the beginning of the string. Similarly, if it is negative then the function extracts from the end.
• length: The third parameter is the length of characters to extract. However, if you don’t pass it then the function returns the whole string from start point.

Return Value

The MySQL SUBSTRING Function returns a substring from a string with the given length and beginning from the starting point.

Examples

Let’s discuss a few examples to use to Substring Function.

Example 1: Substring with Start Point

#Substring beginning from 10th character
mysql> SELECT SUBSTRING('Building Knowledge Together', 10);
+----------------------------------------------+
| SUBSTRING('Building Knowledge Together', 10) |
+----------------------------------------------+
| Knowledge Together                           |
+----------------------------------------------+

#Substring beginning from the 8th character from the end
mysql> SELECT SUBSTRING('Building Knowledge Together', -8);
+----------------------------------------------+
| SUBSTRING('Building Knowledge Together', -8) |
+----------------------------------------------+
| Together                                     |
+----------------------------------------------+

In the above example, we pass the 10 and -8 to the Function. When start point is 10, the function slices the string from 10th character up to the end of the string. Also, when the start point is -8, the function slices the string beginning from the 8th character from the last.

Example 2: Substring with Start Point and Length

For instance, consider the following example with start point and length.

#Start Point 10 and length is 9 characters
mysql> SELECT SUBSTRING('Building Knowledge Together', 10, 9);
+-------------------------------------------------+
| SUBSTRING('Building Knowledge Together', 10, 9) |
+-------------------------------------------------+
| Knowledge                                       |
+-------------------------------------------------+
1 row in set (0.00 sec)

#Start Point -8 and length 8
mysql> SELECT SUBSTRING('Building Knowledge Together', -8, 8);
+-------------------------------------------------+
| SUBSTRING('Building Knowledge Together', -8, 8) |
+-------------------------------------------------+
| Together                                        |
+-------------------------------------------------+
1 row in set (0.00 sec)

Again, in the above example the start points are 10 and -8 respectively. Also, when the length is passed, the MySQL Substring extracts the string containing the provided number of characters.

Example 3: Using with Concat Function

If you want to show three dots (…) after selecting some characters from the database, you can use the Substring Function along with Concat Function. This is shown in the example below.

mysql> SELECT CONCAT(SUBSTRING('Building Knowledge Together', 1, 18), '...');
+----------------------------------------------------------------+
| CONCAT(SUBSTRING('Building Knowledge Together', 1, 18), '...') |
+----------------------------------------------------------------+
| Building Knowledge...                                          |
+----------------------------------------------------------------+

Conclusion

We discussed the MySQL SUBSTRING in this article. You can also read more about MySQL String Functions on Concatly. Also, you can read more about SUBSTR on Official MySQL Documentation.

Also, MySQL SUBSTR and MID Function equal to SUBSTRING Function.