w3resource logo


MySQL substring function

MySQL SUBSTRING() function

rating Average rating 8 out of 10. Total 42 users rated.

<<PreviousNext>>

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

MySQL Substring Function Video

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

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-SUBSTRING() EXAMPLE

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 SUBSTRING() EXAMPLE1

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 SUBSTRING() EXAMPLE2

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

MySQL SUBSTRING() EXAMPLE3

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 SUBSTRING() EXAMPLE4

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 SUBSTRING() EXAMPLE5

All String Functions

MySQL String Functions, slide presentation



We have compiled a number of MySQL Questions and Answers. Check and assess you MySQL skill by visiting those questions and answers.

<<PreviousNext>>