Here, In this post i will show you how to extract the username and domain name from an email address using MySQL. The username is the part before the @, for example "chris" in chris@example.com.
Get the username 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 username 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:
+-------------+ | username | +-------------+ | 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 username,
SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users
And the resulting data:
+-----------+-------------+ | username | domain | +-----------+-------------+ | chris | example.com | +-----------+-------------+ 1 row in set (0.00 sec)
0 Comments