MySQL SUBSTR() function

Last update on April 04 2022 13:12:22 (UTC/GMT +8 hours)

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

Syntax Diagram:

MySQL Version: 5.6

Video Presentation:

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

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

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>

View the example in browser

JSP script:


<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-substr-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT pub_name, SUBSTR(pub_name,4,5) FROM publisher WHERE country='USA'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publishers name</td>
<td>SUBSTR(pub_name,4,5)</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("pub_name")%></TD>
<TD><%=rs.getString("SUBSTR(pub_name,4,5)")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>
</body>
</html>

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

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>

View the example in browser

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

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

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

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

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)

Online Practice Editor:

All String Functions

Previous: STRCMP
Next: SUBSTRING_INDEX