How to select domain name from email address in MySQL?

MySQLMySQLi Database

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)
raja
Published on 16-Jan-2019 07:50:18
Advertisements