MySQL MID() function

Last update on April 04 2022 13:12:03 (UTC/GMT +8 hours)

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.

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 Version: 5.6

Video Presentation:

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);

Sample Output:

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

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

Sample 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)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-mid-function - php mysql examples | w3resource</title>
<meta name="description" content="example-mid-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of Publishers and a string from the name which starting at position four and five characters long:</h2>
<table class='table table-bordered'>
<tr>
<th>Output</th><th>MID(pub_name,4,5)</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name, MID(pub_name,4,5)
FROM publisher
WHERE country="USA"') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['MID(pub_name,4,5)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Online Practice Editor:

All String Functions

Previous: MAKE_SET
Next: NOT LIKE