3

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?

wildplasser
  • 43,142
  • 8
  • 66
  • 109
AZV
  • 35
  • 5
  • 1
    Possible duplicate of [postgresql - replace all instances of a string within text field](https://stackoverflow.com/questions/5060526/postgresql-replace-all-instances-of-a-string-within-text-field) – Evan Carroll Feb 04 '18 at 04:48

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Reference: http://stackoverflow.com/questions/15190078/replace-unicode-characters-in-postgresql – Tim Biegeleisen Sep 28 '16 at 11:22
  • 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. – AZV Sep 28 '16 at 12:20