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!

CodePudding user response：

Use below

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

CodePudding user response：

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

CodePudding user response：

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

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