Showing posts with label Mysql SUBSTR. Show all posts
Showing posts with label Mysql SUBSTR. Show all posts

Tuesday, 30 July 2019

How The SUBSTR() Function Works in MySQL

In MySQL, the SUBSTR() function returns a substring starting from the specified position.
Both SUBSTR() and MID() are synonyms of SUBSTRING().

Syntax

The basic syntax goes like this:
SUBSTR(str,pos,len)
Here, str is the string, pos is the position to start the substring from, and len is an optional argument that determines the number of characters to return from that starting position.
There are several variations on how you can use this function, so the full range of syntaxes looks like this:
SUBSTR(str,pos)
SUBSTR(str FROM pos)
SUBSTR(str,pos,len)
SUBSTR(str FROM pos FOR len)
These are demonstrated in the following examples.

Example 1 – Basic Usage

Here’s an example of the SUBSTR(str,pos) syntax:
SELECT SUBSTR('I play the drums', 3) Result;
Result:
+----------------+
| Result         |
+----------------+
| play the drums |
+----------------+
In this example, I take a substring from the string, starting at position 3.

Example 2 – Using the FROM Clause

Here’s how to do the same thing, but this time using the SUBSTR(str FROM pos) syntax:
SELECT SUBSTR('I play the drums' FROM 3) Result;
Result:
+----------------+
| Result         |
+----------------+
| play the drums |
+----------------+
So we get the same result.
In this case, FROM is standard SQL. Note that this syntax doesn’t use commas.

Example 3 – Specify a Length

In this example, I use the SUBSTR(str,pos,len) syntax:
SELECT SUBSTR('I play the drums', 3, 4) Result;
Result:
+--------+
| Result |
+--------+
| play   |
+--------+
Here I specify that the returned substring should be 4 characters long.

Example 4 – Specify a Length (using the FOR Clause)

In this example, I use the SUBSTR(str FROM pos FOR len) syntax:
SELECT SUBSTR('I play the drums' FROM 3 FOR 5) Result;
Result:
+--------+
| Result |
+--------+
| play   |
+--------+
So this time we used standard SQL to achieve the same result

Tuesday, 10 July 2018

Mysql SUBSTR() function

Mysql 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
NameDescription
strA string from which a substring is to be returned.
posAn integer indicating a string position within the string str.
lenAn integer indicating a 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’.
Code:
SELECT SUBSTR('w3resource',4,3);





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





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





Copy










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





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





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





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




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




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





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

Subscribe to: Posts (Atom)