Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we extract a substring from the value of a column in MySQL table?
We can apply any of the functions like SUBSTRING(), MID() or SUBSTR() to extract a substring from the value of a column. In this case, we must have to provide the name of the column as the first argument of the function i.e. at the place of string we have to give the name of the column. Following example will demonstrate it.
Example
Suppose we want to extract a substring from the ‘Name’ column of ‘Student’ table then it can be done by using the different functions as follows −
mysql> Select name, SUBSTR(name,2,4) from student; +---------+------------------+ | name | SUBSTR(name,2,4) | +---------+------------------+ | Gaurav | aura | | Aarav | arav | | Harshit | arsh | | Gaurav | aura | | Yashraj | ashr | +---------+------------------+ 5 rows in set (0.00 sec) mysql> Select name, MID(name,2,4) from student; +---------+---------------+ | name | MID(name,2,4) | +---------+---------------+ | Gaurav | aura | | Aarav | arav | | Harshit | arsh | | Gaurav | aura | | Yashraj | ashr | +---------+---------------+ 5 rows in set (0.00 sec) mysql> Select name, substring(name,2,4) from student; +---------+---------------------+ | name | substring(name,2,4) | +---------+---------------------+ | Gaurav | aura | | Aarav | arav | | Harshit | arsh | | Gaurav | aura | | Yashraj | ashr | +---------+---------------------+ 5 rows in set (0.00 sec)
We can also apply condition/s in the above queries as follows −
mysql> Select name, substring(name,2,4) from student WHERE address = 'delhi'; +---------+---------------------+ | name | substring(name,2,4) | +---------+---------------------+ | Gaurav | aura | | Harshit | arsh | +---------+---------------------+ 2 rows in set (0.16 sec) mysql> Select name, MID(name,2,4) from student WHERE address = 'delhi'; +---------+---------------+ | name | MID(name,2,4) | +---------+---------------+ | Gaurav | aura | | Harshit | arsh | +---------+---------------+ 2 rows in set (0.00 sec) mysql> Select name, SUBSTR(name,2,4) from student WHERE address = 'delhi'; +---------+------------------+ | name | SUBSTR(name,2,4) | +---------+------------------+ | Gaurav | aura | | Harshit | arsh | +---------+------------------+ 2 rows in set (0.00 sec)
Updated on: 07-Feb-2020
639 Views
Advertisements