MySQL MID() function

MID() function

MySQL MID() extracts a substring from a string. The actual string, position to start extraction and length of the extracted string - all are specified as arguments.

This function is useful in -

• Substring extraction: This function allows us to extract a portion of a string by specifying the starting position and length.
• Text processing: MID() can be used for text processing purposes, such as parsing or analyzing text data. Using specific positions and patterns, you can extract relevant information from a string.

Syntax:

MID(str, pos, len)

Arguments:

The above function is a synonym for SUBSTRING(str,pos,len).

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example of MySQL MID() function

The following MySQL statement extracts 'eso' by starting extraction from the fourth character of 'w3resource' string and extracting 3 following characters.

Code:

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

Output:

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

Extracting a substring from a specific position to the end of the string

The following MySQL statement the MID() function extracts a substring starting from the 6th position of the string 'MySQL Database' until the end of the string.

Code:

SELECT MID('MySQL Database', 6);

Output:

MID('MySQL Database', 6)|
------------------------+
 Database               |

Extracting a substring with negative start position

The following MySQL statement the MID() function extracts the substring starting from the 8th character from the end of the string 'MySQL Database'. Using a negative starting position counts characters from the end of the string.

Code:

SELECT MID('MySQL Database', -8);

Output:

MID('MySQL Database', -8)|
-------------------------+
Database                 |

Example of MySQL MID() function with where clause

The following MySQL statement extracts a substring from the name of the publishers (pub_name column), starting extraction from the fourth character and continue to extract following five characters; but this is done only if the publisher belongs to the USA.

Code:

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

Sample table: publisher

Output:

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

Video Presentation:

All String Functions (Slides presentation)

Previous: MAKE_SET
Next: NOT LIKE