CODEWITHSUNDEEP
google-bigquery
SQL_Learner
SQL_Learner

Reputation: 19

BigQuery to remove a string from an email address whose position is not fixed

Can anyone please help in a query(Using BigQuery/GoogleSQL) that can remove an extra dot com in an email address, for example : [email protected] I have tried using regexp replace but unable to define a search for second dot in the string.

Thank you.

Upvotes: 1

Views: 482

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Consider below approach

select email, regexp_replace(email, r'.com.com$', '.com') as email_out
from your_table

if to apply to dummy data like in below cte

with your_table as (
  select '[email protected]' email union all 
  select '[email protected]' union all
  select '[email protected]' union all
  select '[email protected]' 
)

the output is

enter image description here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

To remove an extra .com from a .com.com ending, use:

SELECT email, REGEXP_REPLACE('\.com\.com$', '.com', email) AS email_out
FROM yourTable;

Upvotes: 0

Related Questions