Admin
Hi, I hope you guys are doing well, In this article, you will see how to get a substring from a string in MySQL with the help of the examples. Throughout this article, we are about to explore two kinds of MySQL string functions to extract some parts of the string.
In real-life projects, Sometimes our requirement is to find some part of a string to perform some operations on that substring, In that scenario, you can use these two functions easily.
Before going further into this article, let’s see a little bit about substring so that you can understand this article easily.
Headings of Contents
What is Substring?
The name substring represents the sub-part of the original string for an instance, You can see the below image, we have two strings original_string and sub_string. The variable original_string has the ‘Programming Funda‘ value and the variable sub_string has the ‘Programming‘ value therefore, sub_string is contained the substring means the ‘Programming‘ word of the original string.
MySQL offers two string functions called SUBSTRING() and SUBSTR() to get the substring of the string.
Now let’s understand these functions one by one with the help of the examples.
MySQL SUBSTRING() Function
It is a function that comes with MySQL string functions, It is used to get some part of a string. The SUBSTRING() function takes some parameters and returns the string of the passed string.
There are various forms of MySQL SUBSTRING() Functions available as you can see below. We will explore all these forms with the help of examples.
- SUBSTRING(str, pos):- In this form of the SUBSTRING() function, str represents the original string and pos represents the position in the original string from where a substring will return. The value of pos can be also negative to get the substring from the end of the string.
- SUBSTRING(str FROM post):- This form of SUBSTRING() function uses FROM standard SQL query. The meaning of this form is also the same as the above form of SUBSTRING().
- SUBSTRING(str, pos, len):- This form takes an additional parameter called len which represents the length of the substring to be returned.
- SUBSTRING(STR FROM post FOR len):- The meaning of this form is also the same as just the above Form, The only difference is that it uses FROM and FOR statements.
MySQL SUBSTR() Function
MySQL SUBSTR() function is the synonym of the SUBSTRING() function. All the form of these functions is also the same as SUBSTRING () function. To use this function, you have to just replace the SUBSTRING() function with SUBSTR() function.
Note:- The value of 0 for pos returns an empty substring and if the len is less than 1 than also empty substring will return.
Note:- The SUBSTRING() and SUBSTR() functions will return NULL, if the any passed argument will be NULL.
let’s see some examples of the above functions.
Getting substring from a string in MySQL
In this example, I will use both the functions SUBSTRING() and SUBSTR() to extract the substring from a string in MySQL and the database column value also.
Example:
In this example, I have extracted a substring from an original string but the original string is hardcoded means it is not coming from a database table.
Using SUBSTR():
mysql> SELECT SUBSTR('Programming Funda', 2) as 'substring';
+------------------+
| substring |
+------------------+
| rogramming Funda |
+------------------+
1 row in set (0.15 sec)
Using SUBSTRING():
mysql> SELECT SUBSTRING('Programming Funda', 2) as 'substring';
+------------------+
| substring |
+------------------+
| rogramming Funda |
+------------------+
1 row in set (0.00 sec)
As you can see in both the above examples, How substring ‘rogramming Funda‘ is being extracted from the original string ‘Programming Funda‘.
Example:
In this example, I have used the second form of the SUBSTRING() and SUBSTR() functions where FROM SQL query is used.
Using SUBSTR():
mysql> SELECT SUBSTR('MySQL Tutorial' FROM 4) as 'substring';
+-------------+
| substring |
+-------------+
| QL Tutorial |
+-------------+
1 row in set (0.00 sec)
Using SUBSTRING():
mysql> SELECT SUBSTRING('MySQL Tutorial' FROM 5) as 'substring';
+------------+
| substring |
+------------+
| L Tutorial |
+------------+
1 row in set (0.00 sec)
Example:
In this example, I have used the third form of the SUBSTRING() and SUBSTR() functions where an additional parameter len is used along with str and pos. The value of len represents the length of the substring that will return.
Using SUBSTR():
mysql> SELECT SUBSTR('MySQL', 3, 2) as 'substring';
+-----------+
| substring |
+-----------+
| SQ |
+-----------+
1 row in set (0.00 sec)
Using SUBSTRING():
mysql> SELECT SUBSTRING('MySQL', 2, 3) as 'substring';
+-----------+
| substring |
+-----------+
| ySQ |
+-----------+
1 row in set (0.00 sec)
Example
In this example, I have used the fourth form of the SUBSTRING() and SUBSTR() function where FROM and FOR statement is used.
Using SUBSTR():
mysql> SELECT SUBSTR('Coding' FROM 3 FOR 2) as 'substring';
+-----------+
| substring |
+-----------+
| di |
+-----------+
1 row in set (0.00 sec)
Using SUBSTRING():
mysql> SELECT SUBSTRING('Coding' FROM 3 FOR 4) as 'substring';
+-----------+
| substring |
+-----------+
| ding |
+-----------+
1 row in set (0.00 sec)
So far we have seen, Process of extracting a substring from an original string but in all the examples original string was hardcoded now, we will how can we extract a substring from the database table column value.
let’s try.
I have created a table just for testing purposes named employee having some records as you can see below.
mysql> SELECT first_name FROM employee; +------------+ | first_name | +------------+ | Vishvajit | | Vinay | | Minakshi | | John | | Akash | | Harshita | | Ayush | | Abhishek | | Vaishanvi | | Mahima | | Arushi | | Mahesh | | Ajay | | Manish | | David | | Nishant | | Darshan | +------------+ 17 rows in set (0.00 sec)
Now, I will use column first_name to get the first three characters using the SUBSTRING() and SUBSTR() functions.
mysql> SELECT
-> first_name,
-> SUBSTRING(first_name FROM 1 FOR 3) AS 'Using SUBSTRING()',
-> SUBSTR(first_name FROM 1 FOR 3) AS 'Using SUBSTR()'
-> FROM
-> employee;
+------------+-------------------+----------------+
| first_name | Using SUBSTRING() | Using SUBSTR() |
+------------+-------------------+----------------+
| Vishvajit | Vis | Vis |
| Vinay | Vin | Vin |
| Minakshi | Min | Min |
| John | Joh | Joh |
| Akash | Aka | Aka |
| Harshita | Har | Har |
| Ayush | Ayu | Ayu |
| Abhishek | Abh | Abh |
| Vaishanvi | Vai | Vai |
| Mahima | Mah | Mah |
| Arushi | Aru | Aru |
| Mahesh | Mah | Mah |
| Ajay | Aja | Aja |
| Manish | Man | Man |
| David | Dav | Dav |
| Nishant | Nis | Nis |
| Darshan | Dar | Dar |
+------------+-------------------+----------------+
17 rows in set (0.00 sec)
In the above query, I have used the first form the MySQL SUBSTRING() and SUBSTR() functions but you can use any one of them.
You can also extract a substring from the end of the string by using a negative pos value. Here pos means a position in the original string from where the extract will start.
For example, let’s extract the last four characters from the first_name column.
mysql> SELECT
-> first_name,
-> SUBSTRING(first_name FROM -4) AS 'Using SUBSTRING()',
-> SUBSTR(first_name FROM -4) AS 'Using SUBSTR()'
-> FROM
-> employee;
+------------+-------------------+----------------+
| first_name | Using SUBSTRING() | Using SUBSTR() |
+------------+-------------------+----------------+
| Vishvajit | ajit | ajit |
| Vinay | inay | inay |
| Minakshi | kshi | kshi |
| John | John | John |
| Akash | kash | kash |
| Harshita | hita | hita |
| Ayush | yush | yush |
| Abhishek | shek | shek |
| Vaishanvi | anvi | anvi |
| Mahima | hima | hima |
| Arushi | ushi | ushi |
| Mahesh | hesh | hesh |
| Ajay | Ajay | Ajay |
| Manish | nish | nish |
| David | avid | avid |
| Nishant | hant | hant |
| Darshan | shan | shan |
+------------+-------------------+----------------+
17 rows in set (0.00 sec)
To get value from the end of the string, You will have to provide a negative pos value for example, I have a string ‘Programming’ and I want to extract the last four characters from it, Then the value of pos will be -4 because -4 represents the 4th character of the string ‘Programming’ from the end.
So, This is how you can extract a substring from an original string. The original string may be hardcoded or any database table column name.
MySQL SUBSTRING() Function Reference:- Click Here MySQL SUBSTR() Function Reference:- Click Here
Related Articles:
- How to get the last day of the month in MySQL
- How to find second highest salary in MySQL
- How to extract month from date in MySQL
- How to extract the year from the month in MySQL
- How to handle Null values in MySQL
- How to show columns of the table in MySQL
- How to import CSV files in MySQL
Summary
Finally, In this article, we have seen how to get a substring from a string in MySQL with the help of examples. You can use any function between SUBSTR() and SUBSTRING() because both are quite similar. You can use it to extract some part of the string as per your requirement or application requirement. You can also use these functions with an integer type of column.
Functions SUBSTRING() and SUBSTR() require string and starting position as an argument and the last argument len always represents the number of characters to extract but it is an optional argument. If you omit the last argument, The functions will return all the strings from starting position.
I hope you found this article helpful. If you like this article, please share and keep visiting for further MySQL tutorials.
Have a nice day….
