CODEWITHSUNDEEP
sqlgoogle-bigquery
Jeremy C
Jeremy C

Reputation: 25

Regex for email pattern

We've noticed a strange email pattern at work for customer emails and want to check them out. Table is Customers

email fullname
[email protected] John Smith
[email protected] Cary Grant

How do we search for this string in BigQuery using Regex? Which is basically the fullname plus 6 random digits (I think I have this part: [0-9]{6}) plus the domain, which is gmail.

I'm not sure how to basically create a regex that accounts for the fullname plus random 6 digits plus the domain (gmail). Any help is appreciated!

Upvotes: -1

Views: 426

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Use below

select * 
from  your_table
where regexp_contains(lower(email), r'^' || replace(lower(fullname), ' ', '') || '\d{6}@gmail.com$')

Upvotes: 0

Jeremy C
Jeremy C

Reputation: 25

I figured it out! Thank you for everybody's help

where email = concat(fullname, regexp_extract(email, '[0-9]{6}@gmail.com'))

Upvotes: 1

flaredev
flaredev

Reputation: 369

SELECT * FROM Customers WHERE email LIKE fullname + '%[0-9]@gmail.com'

Upvotes: 0

Related Questions