How the MID() Function Works in MySQL

In MySQL, the MID() function returns a substring starting from the specified position.

Both MID() and SUBSTR() are synonyms of SUBSTRING().

Syntax

The basic syntax goes like this:

MID(str,pos,len)

Here, str is the string, pos is the position to start the substring from, and len is an optional argument that determines the number of characters to return from that starting position.

There are several variations on how you can use this function, so the full range of syntaxes looks like this:

MID(str,pos)
MID(str FROM pos)
MID(str,pos,len)
MID(str FROM pos FOR len)

These are demonstrated in the following examples.

Example 1 – Basic Usage

Here’s an example of the MID(str,pos) syntax:

SELECT MID('I drink coffee', 3) Result;

Result:

+--------------+
| Result       |
+--------------+
| drink coffee |
+--------------+

In this example, I take a substring from the string, starting at position 3.

Example 2 – Using the FROM Clause

Here’s how to do the same thing, but this time using the MID(str FROM pos) syntax:

SELECT MID('I drink coffee' FROM 3) Result;

Result:

+--------------+
| Result       |
+--------------+
| drink coffee |
+--------------+

So we get the same result.

In this case, FROM is standard SQL. Note that this syntax doesn’t use commas.

Example 3 – Specify a Length

In this example, I use the MID(str,pos,len) syntax:

SELECT MID('I drink coffee', 3, 5) Result;

Result:

+--------+
| Result |
+--------+
| drink  |
+--------+

Here I specify that the returned substring should be 5 characters long.

Example 4 – Specify a Length (using the FOR Clause)

In this example, I use the MID(str FROM pos FOR len) syntax:

SELECT MID('I drink coffee' FROM 3 FOR 5) Result;

Result:

+--------+
| Result |
+--------+
| drink  |
+--------+

So this time we used standard SQL to achieve the same result