w3resource logo


MySQL substr function

MySQL SUBSTR() function

rating MySQL substr() function has average rating 7 out of 10. Total 11 users rated.

<<PreviousNext>>

Description

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 number of characters to be returned.

The above function is a synonym for SUBSTRING().

MySQL Version : 5.6

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

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

Output

MySQL SUBSTR()

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.

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

Sample table : publisher

Output

MySQL SUBSTR() 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>example2-substr-function - php mysql examples | w3resource</title>
</head>
<body>
<?php
echo "<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>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='250' align='center'>Publishers name</td><td width='250' 
align='center'>SUBSTR(pub_name,4,5)</td>
";
echo "</tr>";
include("../dbopen.php");
$result = mysql_query("SELECT pub_name, SUBSTR(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['SUBSTR(pub_name,4,5)'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html> 

View the example in browser

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.

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

Sample table : publisher

Output

MySQL SUBSTR() EXAMPLE1

PHP script

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xml ns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>example3-substr-function - php mysql examples | w3resource</title>
</head>
<body>
<?php
echo "<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>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>"; echo "<td width='250' align='center'>Publishers name</td><td width='250' align='center'>SUBSTR(pub_name,5)</td> "; echo "</tr>"; include("../dbopen.php"); $result = mysql_query("SELECT pub_name, SUBSTR(pub_name,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['SUBSTR(pub_name,5)'] . "</td>"; echo "</tr>"; } echo "</table>"; ?> </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.

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

Output

MySQL SUBSTR() EXAMPLE2

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.

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

Sample table : publisher

Output

MySQL SUBSTR() EXAMPLE3

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.

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

Sample table : publisher

Output

MySQL SUBSTR() EXAMPLE4

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.

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

Sample table : publisher

Output

MySQL SUBSTR() EXAMPLE5



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

<<PreviousNext>>