regex-email.md

RegEx: Validate Email Addresses

View an interactive version of this snippet here.

Description

The only real way to validate an email address is to send a request to it and observe the response, but for the purposes of analytics it's often useful to strip out rows including malformed email addresses. Using the SUBSTRING function, the email validation RegEx comes courtesy of Evan Carroll:

Snippet ✂️

WITH data AS (
    SELECT UNNEST(ARRAY[
    'a',
    '[email protected]',
    '[email protected]',
    '@count.co',
    '[email protected]'
    ]) AS emails
)

SELECT
  emails,
  CASE WHEN
    SUBSTRING(
        LOWER(emails),
        '^[a-zA-Z0-9.!#$%&''''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
    ) IS NOT NULL
    THEN TRUE
   ELSE FALSE
  END AS is_valid
FROM data

emails	is_valid
a	false
[email protected]	true
[email protected]	true
@count.co	false
[email protected]	true