Working with Email Addresses in SQL

gistfile1.txt

/// QUERY - Checking if an email address if valid ///

SELECT email,
       email REGEXP '^[A-Za-z0-9._%\-+!#$&/=?^|~]+@[A-Za-z0-9.-]+[.][A-Za-z]+$' AS valid_email
  FROM email_table


/// RESULT ///

email                   | valid_email
----                    | ----
jerry@gmail.com         | 1
gerge@vandalay.com      | 1
ebenes@tufts.edu        | 1 
kramr.$@cofee_table^^   | 0



/// QUERY - Splitting an email into local and domain names ///

SELECT email,
       SUBSTRING_INDEX(email,'@',1) AS user_name,
       SUBSTRING_INDEX(email,'@',-1) AS domain_name
  FROM email_table


/// RESULT ///

email                   | user_name   | domain_name
----                    | ----        | ----
jerry@gmail.com         | jerry       | gmail.com
gerge@vandalay.com      | gerge       | vandalay.com
ebenes@tufts.edu        | ebenes      | tufts.edu



/// QUERY - Checking for consumer emails ///

SELECT e.email,
       CASE WHEN c.domain IS NOT NULL THEN 'consumer' ELSE 'business' END AS email_type
  FROM email_table e
  JOIN consumer_domains c
    ON c.domain = SUBSTRING_INDEX(email,'@',-1)


/// RESULT ///

email                   | email_type
----                    | ----    
jerry@gmail.com         | consumer
gerge@vandalay.com      | business
ebenes@tufts.edu        | business