w3resource

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:

Name Description
str A string from which a substring will be extracted.
pos An integer indicating the position (within the str) from where extraction should start.
len An integer indicating the length of the extracted string.

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

Syntax Diagram:

MySQL MID() Function - Syntax Diagram

MySQL Version: 8.0

Pictorial Presentation:

MySQL MID() 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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql/string-functions/mysql-mid-function.php