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 to select domain name from email address in MySQL?
To select domain name from email address, you can use in-built SUBSTRING_INDEX() function from MySQL.
To understand the concept, let us create a table. The following is the query to create a table.
mysql> create table selectDomainNameOnly −> ( −> UserEmailAddress varchar(200) −> ); Query OK, 0 rows affected (0.52 sec)
Insert records in the table using insert command. The record will have email-ids from which we need to fetch the domain name. The query is as follows −
mysql> insert into selectDomainNameOnly values('John123@yahoo.com');
Query OK, 1 row affected (0.10 sec)
mysql> insert into selectDomainNameOnly values('234Bob@gmail.com');
Query OK, 1 row affected (0.24 sec)
mysql> insert into selectDomainNameOnly values('Carol23595@hotmail.com');
Query OK, 1 row affected (0.16 sec)Display all records from table using select statement. The query is as follows −
mysql> select *from selectDomainNameOnly;
The following is the output −
+------------------------+ | UserEmailAddress | +------------------------+ | John123@yahoo.com | | 234Bob@gmail.com | | Carol23595@hotmail.com | +------------------------+ 3 rows in set (0.00 sec)
The following is the query to select only domain name. The query is as follows −
mysql> select (SUBSTRING_INDEX(SUBSTR(UserEmailAddress, INSTR(UserEmailAddress, '@') + 1),'.',1)) as DomainName from selectDomainNameOnly;
The following is the output −
+------------+ | DomainName | +------------+ | yahoo | | gmail | | hotmail | +------------+ 3 rows in set (0.06 sec)
Updated on: 30-Jul-2019
3K+ Views
Advertisements