Results 1 to 7 of 7

Thread: MySQL REGEX

  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: MySQL REGEX

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which domain are you searching for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •