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.
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.0">
<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>
Online Practice Editor:
All String Functions
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises