Mysql SUBSTR() function ~ Tech Blog

Tuesday, 10 July 2018

Mysql SUBSTR() function

MySQL SUBSTR() returns the specified number of characters from a particular position of a given string. SUBSTR() is a synonym for SUBSTRING().

Syntax:

SUBSTR(str, pos, len)

Arguments

Name	Description
str	A string from which a substring is to be returned.
pos	An integer indicating a string position within the string str.
len	An integer indicating a number of characters to be returned.

The above function is a synonym for SUBSTRING().

MySQL Version: 5.6

Pictorial Presentation

The SUBSTR() function is same as SUBSTRING() function

Example : MySQL SUBSTR() function

The following MySQL statement returns 3 numbers of characters from the 4th position of the string ‘w3resource’.

Code:

SELECT SUBSTR('w3resource',4,3);



Copy

Sample Output:

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

Example of MySQL SUBSTR() using table

The following MySQL statement returns 5 numbers of characters from the 4th position of the column pub_name for those publishers which belongs to the country ‘USA’ from the table publisher.

Code:

SELECT pub_name, SUBSTR(pub_name,4,5) 
FROM publisher 
WHERE country='USA';



Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTR(pub_name,4,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------+
| pub_name                 | SUBSTR(pub_name,4,5) |
+--------------------------+----------------------+
| Jex Max Publication      |  Max                 | 
| Mountain Publication     | ntain                | 
| Summer Night Publication | mer N                | 
+--------------------------+----------------------+
3 rows in set (0.00 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-substr-function - php mysql examples | w3resource</title>
<meta name="description" content="example-substr-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 those who belong to USA. Second column shows 
a string containing 5 characters from the fourth position of the Publisher's name:</h2>
<table class='table table-bordered'>
<tr>
<th>Publishers name</th><th>SUBSTR(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, SUBSTR(pub_name,4,5)
FROM publisher
WHERE country="USA"') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['SUBSTR(pub_name,4,5)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>



Copy





Copy

Example of MySQL SUBSTR() function extracts rest characters from a specific position

The following MySQL statement returns the rest of the characters from the 5th position of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Code:

SELECT pub_name, SUBSTR(pub_name,5)
FROM publisher 
WHERE country='USA';



Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTR(pub_name,5)
    -> FROM publisher 
    -> WHERE country='USA'; 
+--------------------------+----------------------+
| pub_name                 | SUBSTR(pub_name,5)   |
+--------------------------+----------------------+
| Jex Max Publication      | Max Publication      | 
| Mountain Publication     | tain Publication     | 
| Summer Night Publication | er Night Publication | 
+--------------------------+----------------------+
3 rows in set (0.00 sec)

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example1-substr-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-substr-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 those who belong to USA. Second column shows 
a string from the name of the Publisher starting at fifth position of the name:</h2>
<table class='table table-bordered'>
<tr>
<th>Publishers name</th><th>SUBSTR(pub_name,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, SUBSTR(pub_name,5) 
FROM publisher 
WHERE country="USA"') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['SUBSTR(pub_name,5)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>



Copy

Example of MySQL SUBSTR() using FROM keyword

The following MySQL statement returns the rest of the characters from the 5th position (notice that FROM keyword is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Code:

SELECT pub_name, 
SUBSTR(pub_name FROM 5) 
FROM publisher 
WHERE country='USA';



Copy

Sample Output:

mysql> SELECT pub_name, 
    -> SUBSTR(pub_name FROM 5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+-------------------------+
| pub_name                 | SUBSTR(pub_name FROM 5) |
+--------------------------+-------------------------+
| Jex Max Publication      | Max Publication         | 
| Mountain Publication     | tain Publication        | 
| Summer Night Publication | er Night Publication    | 
+--------------------------+-------------------------+
3 rows in set (0.00 sec)

Example of MySQL SUBSTR() extracts from negative position

The following MySQL statement returns the rest of the characters from the 4th position from the end (since -4 is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Code:

SELECT pub_name, SUBSTR(pub_name ,-4) 
FROM publisher 
WHERE country='USA'; 
           


Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTR(pub_name ,-4) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------+
| pub_name                 | SUBSTR(pub_name ,-4) |
+--------------------------+----------------------+
| Jex Max Publication      | tion                 | 
| Mountain Publication     | tion                 | 
| Summer Night Publication | tion                 | 
+--------------------------+----------------------+
3 rows in set (0.02 sec)

Example of MySQL SUBSTR() extracting from the end

The following MySQL statement returns 5 characters from the 15th position from the end (since -15 is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Code:

SELECT pub_name, SUBSTR(pub_name ,-15,5)
FROM publisher 
WHERE country='USA';
               


Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTR(pub_name ,-15,5)
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+-------------------------+
| pub_name                 | SUBSTR(pub_name ,-15,5) |
+--------------------------+-------------------------+
| Jex Max Publication      | Max P                   | 
| Mountain Publication     | ain P                   | 
| Summer Night Publication | ght P                   | 
+--------------------------+-------------------------+
3 rows in set (0.00 sec)

Example MySQL SUBSTR() with FROM and FOR keywords

The following MySQL statement returns 5 characters (notice that FOR keyword is used here) from the 15th position (notice that FROMkeyword is used here) from the end (since -15 is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Code:

SELECT pub_name, SUBSTR(pub_name 
FROM -15 FOR 5) 
FROM publisher 
WHERE country='USA';



Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTR(pub_name FROM -15 FOR 5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+---------------------------------+
| pub_name                 | SUBSTR(pub_name FROM -15 FOR 5) |
+--------------------------+---------------------------------+
| Jex Max Publication      | Max P                           | 
| Mountain Publication     | ain P                           | 
| Summer Night Publication | ght P                           | 
+--------------------------+---------------------------------+
3 rows in set (0.00 sec)

Tech Blog

Tuesday, 10 July 2018