PostgreSQL - replace all instances of particular character in table columns

Question

I have table article which contains columns like title, author, headline, body, subheading, abstract, and some more. I'm trying to replace all instances of particular symbol in all aforementioned columns at once (but not in the others) but I must say that working on SQL and databases isn't anything I know much about...

I've found this line here on stackoverflow, however after editing it into what I thought would work I see no effect. I hoped to start with just title column to see the results:

UPDATE article SET title = replace(title, '<U+2029>', ' ');

But it didn't change anything.

Can somebody explain to me what am I doing wrong and what to write in PostgreSQL to get what I need?

Possible duplicate of postgresql - replace all instances of a string within text field — Evan Carroll
– Evan Carroll, Commented Feb 4, 2018 at 4:48

Gordon Linoff · Accepted Answer · 2016-09-28 18:53:06Z

4

If that is a unicode constant, then you can try this:

UPDATE article
    SET title = regexp_replace(title, U&'\2029', ' ', 'g');

This assumes that 2029 is the hexadecimal representation of the character.

edited Sep 28, 2016 at 18:53

answered Sep 28, 2016 at 11:20

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

Tim Biegeleisen Over a year ago

Reference: stackoverflow.com/questions/15190078/…

AZV Over a year ago

Thanks, you just saved me from manually editing a couple of thousand entries! Just one thing: it should be: ...' ', 'g');, didn't work without apostrophes around g.

Collectives™ on Stack Overflow

PostgreSQL - replace all instances of particular character in table columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related