Syntax error How to select domain name from email address in MySQL?

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: 2019-07-30T22:30:24+05:30

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements