w3resource
MYSQL Tutorial

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 MID() Function - Syntax Diagram

MySQL Version: 5.6

Video Presentation

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

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

All String Functions

MySQL String Functions, slide presentation