w3resource logo


MySQL substring function

MySQL SUBSTRING() function

<<PreviousNext>>

Seondary Nav

Description

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.

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’.

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

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.

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

Sample table : publisher

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 PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>example-substring-function - php mysql examples | w3resource</title>
</head>
<body>
<?php
echo "<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>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='250' align='center'>Publishers</td><td width='250' 
align='center'>SUBSTRING(pub_name,4,5)</td>";
echo "</tr>";
include("../dbopen.php");
$result = mysql_query("SELECT pub_name, SUBSTRING(pub_name,4,5)
FROM publisher
WHERE country='USA'");
while($row=mysql_fetch_array($result))
{
echo "<tr>";
echo "<td align='center' width='200'>" . $row['pub_name'] . "</td>";
echo "<td align='center' width='200'>" . $row['SUBSTRING(pub_name,4,5)'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html> 

View the example in browser

Example MySQL SUBSTRING() extracts rest characters

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.

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

Sample table : publisher

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.

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

Sample table : publisher

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.

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

Sample table : publisher

Output

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

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.

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

Sample table : publisher

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

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

Sample table : publisher

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)

All String Functions

MySQL String Functions, slide presentation



<<PreviousNext>>

Looking for some other tutorial?