Mysql MID() function ~ Tech Blog

Mysql 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).

MySQL Version: 5.6

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



Copy

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



Copy

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>

Tech Blog

Tuesday, 10 July 2018