CODEWITHSUNDEEP
sqlregexgoogle-bigquery
KIRIT BHATT
KIRIT BHATT

Reputation: 71

Validating Email addresses in my google bigquery database by using mysql regular expression

I am trying to validate email addresses in my google bigquery table. I am using following query to do that but bigquery is throwing an error. I am not sure what is wrong in my code. I found the regex for validation on below story:- What characters are allowed in an email address?

select email
FROM my table
WHERE REGEXP_CONTAINS(email, regex)

here regex is the

Perl's RFC2822 regex

Upvotes: 5

Views: 4351

Answers (2)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

Try this query

SELECT email
FROM table
where email REGEXP '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,6}'

Note that the last part {2,6} will limit the top domain name to only 6 characthers long, as pointed out by @smoore4.

SELECT email
FROM table
where email REGEXP '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}'

This will only demand a minimum length of 2 from the top domain.

Upvotes: 5

Yerachmiel Elimelech Weiss
Yerachmiel Elimelech Weiss

Reputation: 220

I think this is cleaner...

SELECT email 
FROM table 
where REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+")

Upvotes: 0

Related Questions