MySQL MID Function

The MID function in MySQL is used to extract a portion of a string or a substring starting from a specific position with a specified length.

The syntax for the MID function is as follows: 

MID(str, start, length)

Here:

1. str is the string from which we want to extract a portion.
2. start is the position from which we want to start extracting the substring. The first position in a string is 1.
3. length is the number of characters to be extracted from the given string.

What is in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

MID instruction example:

mysql> SELECT MID(product, 1, 3) AS First3Report
    -> FROm Stores;
+--------------+
| First3Report |
+--------------+
| Gol          |
| Gol          |
| Gre          |
| XYZ          |
| Ext          |
| LPT          |
| COM          |
| NIC          |
+--------------+
8 rows in set (0.00 sec)

mysql>

Our code select the first three characters of the "product" column from the "Stores" table and gives it an alias "First3Report". 

Not really useful, but why not:

mysql> SELECT MID(city, 2, 4) AS WeirdReport FROm Stores;
+-------------+
| WeirdReport |
+-------------+
| ondo        |
| ondo        |
| erli        |
| erli        |
| osco        |
| osco        |
| iami        |
| aris        |
+-------------+
8 rows in set (0.00 sec)

mysql>

In this case, the starting position is 2, so the function will skip the first character of each city name. 

The length of the substring is 4, so the function will return the next four characters of each city name. The resulting column is given the alias "WeirdReport".

The output of this code shows the resulting substring of each city name for each row in the table.