Duplicate quotes in a postgres string

Question

I have a function that generates a new user in the database. I would like to check if the email address contains any quotes and if it does, I would like to duplicate them.

For example, I have the following email address: test.o'[email protected] and I would like to transform it into test.o''[email protected].

Could anybody help me with this? Thank you

Tim Biegeleisen · Accepted Answer · 2021-11-03 08:38:48Z

2

Assuming you expect only one single quote (and not double or more), you could try using a simple replace:

UPDATE yourTable
SET email = REPLACE(email, '''', '''''');

answered Nov 3, 2021 at 8:38

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

5 Comments

Ergest Basha Over a year ago

Maybe for better reading and understanding you can use double quotes like : REPLACE(email, "'", "''")

Tim Biegeleisen Over a year ago

@ErgestBasha OK, but I won't quote you on that :-) ... Double quotes may not be supported by all SQL engines, so I tried to give an answer that is as ANSI-friendly as possible.

jarlh Over a year ago

@ErgestBasha, doesn't Postgresql read "'" as a column name?

jarlh Over a year ago

Anyway, I'd add WHERE email LIKE '%''%', to keep transaction size down.

Ergest Basha Over a year ago

@jarlh you are right I didn't notice PostgreSQL tag

Collectives™ on Stack Overflow

Duplicate quotes in a postgres string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related