postgresql regexp_replace with dots

Question

I want to replace strings between @ and . in email address when selecting from PostgreSQL.

so far I can replace @ and everything after it

select REGEXP_REPLACE(contactemail, '@(.*)(\.)?', '@xxx') from customer;

SELECT REGEXP_REPLACE('[email protected]', '@[^\.]+\.', '@xxx.') -> [email protected] — 404
– 404, Commented Nov 5, 2019 at 12:10
Your second version works well, it replaces everything between @ and first dot. Thank you — user3757753
– user3757753, Commented Nov 5, 2019 at 12:23

Wiktor Stribiżew · Accepted Answer · 2019-11-05 22:34:27Z

1

Your @(.*)(\.)? pattern matches a @, then captures into Group 1 any 0+ chars other than line break chars as many as possible, and then tries to match an optional dot, i.e. this (\.)? will never match any text but empty. It is no wonder you match all after @ with the pattern.

You need to use

SELECT REGEXP_REPLACE(col, '@[^.]*\.', '@xxx.')

Or, if you want to only remove the part of text between the closest @ and .:

SELECT REGEXP_REPLACE(col, '@[^@.]*\.', '@xxx.')

The pattern matches

@ - a @ char
[^.]* - zero or more occurrences of any char but .
\. - a dot.

answered Nov 5, 2019 at 22:34

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql regexp_replace with dots

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related