MySQL REGEX

ozzii · #1 (**permalink**) 07-26-09, 09:13

Hi

does anybody know what the regular expression would be to match the domain part of an email address?

e.g

I have database column with email addresses. I would like to select all emails that have the same domain part i.e @dbforums.com

i use the following

Code:

SELECT SUBSTRING_INDEX(inEmail, '@', -1)
	 INTO emailDomain;
	  
  SELECT email
  	FROM tbl_employees
   WHERE email REGEXP CONCAT('@', emailDomain, '$')

is there anyway i can do this by just using a REGEXP without having to do the substring bit to retrieve the domain part?

r937 · #2 (**permalink**) 07-26-09, 09:35

the problem here is that you need to specify which domain you're after

because what you posted will return all emails in the table, and therefore you don't really need to "do the substring bit to retrieve the domain part"

if you're after the emails for a specific domain, just do this:

Code:

 WHERE email LIKE '%@example.com'

ozzii · #3 (**permalink**) 07-26-09, 11:10

Quote:

Originally Posted by r937

the problem here is that you need to specify which domain you're after

because what you posted will return all emails in the table, and therefore you don't really need to "do the substring bit to retrieve the domain part"

if you're after the emails for a specific domain, just do this:

Code:

 WHERE email LIKE '%@example.com'

with the above I would still need to extract the domain part first before i can use the LIKE clause which doesn't solve the problem. What i want to do is use the full email address and the regular expression to match just the domain part. This would save me from doing the substring bit to retrieve the domain part to search on.

r937 · #4 (**permalink**) 07-26-09, 14:43

which domain are you searching for?

ozzii · #5 (**permalink**) 07-26-09, 15:54

it can be any domain. For example a company employee wants to register a user account. The employee registers using their email address and a password. Before an account is created I want to check the database to see if there are other employees registered with the same email domain.

r937 · #6 (**permalink**) 07-26-09, 17:29

Quote:

Originally Posted by ozzii

it can be any domain. For example a company employee wants to register a user account. The employee registers using their email address and a password. Before an account is created I want to check the database to see if there are other employees registered with the same email domain.

but the point is, you are searching for a specific domain, yes?

then all you need is a WHERE cluase like in post #2

mike_bike_kite · #7 (**permalink**) 07-27-09, 08:25

Couldn't you just run the following SQL and see if it returns a name. If it does then an account with the same email domain already exists. If it doesn't then it's a new domain:

Code:

select email as existing_domain
from   tbl_employees
where  SUBSTRING_INDEX(email, '@', -1) = SUBSTRING_INDEX("new_name@gs.com", '@', -1)
limit  1;

Obviously the "new_name@gs.com" will be a variable holding the email of the new account.

Mike

Internet Services

Web Development

Digital Marketing

Consumer Tech