MySQL MID Function | Extract Substring from String

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

Syntax

MID(string, start, length)

Parameters

The MySQL MID 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 MySQL MID 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 MID 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 MID Function in MySQL.

Example 1: Substring with Start Point

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

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

In the above example, we pass the 10 and -8 to the MySQL MID. 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 MID('Building Knowledge Together', 10, 9);
+-------------------------------------------------+
| MID('Building Knowledge Together', 10, 9) |
+-------------------------------------------------+
| Knowledge                                       |
+-------------------------------------------------+
1 row in set (0.00 sec)

#Start Point -8 and length 8
mysql> SELECT MID('Building Knowledge Together', -8, 8);
+-------------------------------------------------+
| MID('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 MID Function 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(MID('Building Knowledge Together', 1, 18), '...');
+----------------------------------------------------------------+
| CONCAT(MID('Building Knowledge Together', 1, 18), '...') |
+----------------------------------------------------------------+
| Building Knowledge...                                          |
+----------------------------------------------------------------+

Conclusion

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

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