3

How do i replace a single apostrophe ' in a string ie;

Turn King's lynn to Kings lynn

Something like

select replace ('King's lynn',''','')

Have tried ''\' but doesn't escape

mapping dom
  • 1,737
  • 4
  • 27
  • 50
  • 2
    It's `'\''`, not `''\'`. That is, the escape character (``\``) preceeds the character being escaped (`'`). – Phylogenesis Aug 18 '17 at 14:01
  • 2
    I should note that escape quoting like this in PostgreSQL [requires an `E` prefix](https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE), such as ``E'\''``. – Phylogenesis Aug 18 '17 at 14:09
  • Does this answer your question? [Insert text with single quotes in PostgreSQL](https://stackoverflow.com/questions/12316953/insert-text-with-single-quotes-in-postgresql) – Bergi Jul 03 '23 at 10:57

3 Answers3

8

Dollar quoting makes it easier:

replace ($$King's lynn$$, $$'$$, '')
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
1

Use two single quotes to escape the character. In your case it would mean 4 quotes in a row.

select replace ('King''s lynn','''','')

...or...

select replace ('King\'s lynn','\'','')
Momus
  • 394
  • 2
  • 13
0

I always use CHR() for apostrophe manipulation as it doesn't require learning a new syntax in each DB language...

REPLACE('King's lynn', CHR(39), CHR(39)||CHR(39))
Dempsey Appelt
  • 1