MySQL queries to get the local part and domain from an email address

Posted in MySql -

This post shows how to extract the local part and domain name from an email address using MySQL. The local part is the part before the @, for example "chris" in chris@example.com.

Get the local part of the email address

Let's assume the email address column is called "email" and is in the "users" table, and that all email addresses in the database are valid with an @ in them.

SELECT SUBSTRING(email, 1, LOCATE('@', email) - 1) AS localpart FROM users;

This gets the first part of the email address up to the @ symbol, so the resulting data for "chris@example.com" would look like this:

+-------------+
| localpart   |
+-------------+
| chris       |
+-------------+
1 row in set (0.00 sec)

Get the domain part of the email address

SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users;

This gets everything after the @ symbol, so the resulting data for "chris@example.com" would look like this:

+-------------+
| domain      |
+-------------+
| example.com |
+-------------+
1 row in set (0.00 sec)

Getting both at the same time

There's no reason why you shouldn't put both into the same query:

SELECT SUBSTRING(email, 1, LOCATE('@', email) - 1) AS localpart,
       SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM   users

And the resulting data:

+-----------+-------------+
| localpart | domain      |
+-----------+-------------+
| chris     | example.com |
+-----------+-------------+
1 row in set (0.00 sec)


Related posts:


Comments