Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

MySQL SUBSTRING() function

SUBSTRING() function

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

Syntax:

SUBSTRING(str, pos, len)

OR

SUBSTRING(str FROM pos FOR len)

Arguments:

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

Syntax Diagram:

MySQL SUBSTRING() Function - Syntax Diagram

MySQL Version: 5.6

Video Presentation

Pictorial representation

pictorial representation of MySQL substring function

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

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

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>

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-substring-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, SUBSTRING(pub_name,4,5) FROM publisher WHERE country='USA'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publishers</td>
<td>SUBSTRING(pub_name,4,5)</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("pub_name")%></TD>
<TD><%=rs.getString("SUBSTRING(pub_name,4,5)")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>
</body>
</html>

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

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

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

Sample table: publisher


Sample Output:

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

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

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

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)

Online Practice Editor:


All String Functions

MySQL String Functions, slide presentation

Previous: SUBSTRING_INDEX
Next: TRIM