Replace characters with multi-character strings

Question

I am trying to replace German and Dutch umlauts such as ä, ü, or ß. They should be written like ae instead of ä. So I can't simply translate one char with another.

Is there a more elegant way to do that? Actually it looks like that (not completed yet):

SELECT addr, REPLACE (REPLACE(addr, 'ü','ue'),'ß','ss') FROM search;

On my way trying different commands I got another problem:

When I searched for Ü I got this:

ERROR: invalid byte sequence for encoding "UTF8": 0xdc27

Tried it with U&'\0220', it didn't replace anything. Only by using ü (for lowercase ü) it was replaced correctly. Has to do something with unicode, but how to solve this issue?

Kind regards from Germany. :)

Answer 1

Your server encoding seems to be UTF8.
I suspect your client_encoding does not match, which might give you a wrong impression of what you are dealing with. Check with:

SHOW client_encoding;   -- in your actual session

And read this related answers:
Can not insert German characters in Postgres
Replace unicode characters in PostgreSQL

The rest of the tool chain has to be in sync, too. When using puTTY, for instance, one has to make sure, the terminal agrees with the rest: Change settings... Window -> Translation -> Remote character set = UTF-8.

As for your first question, you already have the best solution. A couple of umlauts are best replaced with a string of replace() statements.

As you seem to know already as well, single character replacements are more efficient with (a single) translate() statement.

Related:

• Replace unicode characters in PostgreSQL
• Regex remove all occurrences of multiple characters in a string