Mysql SUBSTRING() function ~ Tech Blog

Tuesday, 10 July 2018

Mysql SUBSTRING() function

MySQL SUBSTRING() returns a specified number of characters from a particular position of a given string.

Syntax:

SUBSTRING(str, pos, len)

SUBSTRING(str FROM pos FOR len)

Arguments

Name	Description
str	A string.
pos	Starting position.
len	Length in characters.

Pictorial representation

Example : MySQL SUBSTRING() function

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

Code:

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



Copy

Sample Output:

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

Example MySQL SUBSTRING() function using table

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

Code:

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



Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTRING(pub_name,4,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+-------------------------+
| pub_name                 | SUBSTRING(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-substring-function - php mysql examples | w3resource</title>
<meta name="description" content="example-substring-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 and 
a substring from the name which is starting at position four and five 
characters long:</h2>
<table class='table table-bordered'>
<tr>
<th>Publishers</th><th>SUBSTRING(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, SUBSTRING(pub_name,4,5)
FROM publisher
WHERE country="USA"') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['SUBSTRING(pub_name,4,5)'] . "</td>";
echo "</tr>";  
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>



Copy





Copy

The following MySQL statement returns the remaining 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, SUBSTRING(pub_name,5) 
FROM publisher 
WHERE country='USA';



Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTRING(pub_name,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+-----------------------+
| pub_name                 | SUBSTRING(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)

Example MySQL SUBSTRING() function with FROM keyword

The following MySQL statement returns the remaining 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, SUBSTRING(pub_name FROM 5) 
FROM publisher 
WHERE country='USA';



Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTRING(pub_name FROM 5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------------+
| pub_name                 | SUBSTRING(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 SUBSTRING() to extracts from a negative position

The following MySQL statement returns the remaining characters from the 4th position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case the value of position of the beginning of the retrieval is negative. So the beginning of the retrieval is 4 characters from the end of the string rather than the beginning.

Code:

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



Copy

Sample table: publisher

Sample Output:

Example of MySQL SUBSTRING() function extracts from the end

The following MySQL statement returns the 5 number of characters from the 15th position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case the value of position of the beginning of the retrieval is negative. So the beginning of the retrieval is 15 characters from the end of the string rather than the beginning.

Code:

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



Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, SUBSTRING(pub_name ,-15,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------------+
| pub_name                 | SUBSTRING(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 of MySQL SUBSTRING() using FROM and FOR keywords

The following MySQL statement returns the 5 (Notice that FOR keyword is used) numbers of characters from the 15th (Notice that FROM keyword is used) position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case, the value of the position of the beginning of the retrieval is negative. So the beginning of the retrieval is 15 characters from the end of the string rather than the beginning.

Code:

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



Copy

Sample table: publisher

Sample Output:

mysql> SELECT pub_name, 
    -> SUBSTRING(pub_name FROM -15 FOR 5) 
    -> FROM publisher 
    -> WHERE country='USA'; 
+--------------------------+------------------------------------+
| pub_name                 | SUBSTRING(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