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)
