regex-email.md

Regex: Validate email address

Explore this snippet here.

Description

The only real way to validate an email address is to send an email to it, but for the purposes of analytics it's often useful to strip out rows including malformed email address. Using the REGEXP_CONTAINS function, this example comes courtesy of Reddit user chrisgoddard:

with emails as (
  select * from unnest([
    'a',
    '[email protected]',
    '[email protected]',
    '@count.co',
    '[email protected]'
  ]) as email
)

select
  email,
  regexp_contains(
    lower(email),
    "^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])$"
  ) valid
from emails