Postgres中解码URL内UTF-16 Unicode字符的最优方法

直接用replace逐个替换特定转义字符显然不现实，毕竟Unicode转义序列有成千上万种。这里提供一个通用的解决方案，通过自定义函数批量处理所有uXXXX格式的Unicode转义：

自定义解码函数 #

创建一个PL/pgSQL函数，利用正则匹配所有u开头的四位十六进制Unicode码点，将其转换为对应字符：

CREATE OR REPLACE FUNCTION decode_unicode_url(url text) RETURNS text AS $$
BEGIN
  -- 全局匹配uXXXX格式的转义，将十六进制码点转为十进制后用chr生成对应字符
  RETURN regexp_replace(url, 'u([0-9A-Fa-f]{4})', chr(to_number('\1', 'XXXX')), 'g');
END;
$$ LANGUAGE plpgsql;

使用示例 #

针对你给出的URL，直接调用函数即可完成解码：

SELECT decode_unicode_url('https://example.com/xyz/ps5 playstation 5?sortu003dnewest');

执行后会返回：

https://example.com/xyz/ps5 playstation 5?sort=newest

扩展说明 #

• 这个函数能处理所有uXXXX格式的UTF-16基本平面转义（比如u0026转&、u0020转空格等），覆盖绝大多数URL场景。
• 如果需要处理UTF-16代理对（比如表情符号的\uD83D\uDE00），需要额外逻辑拆分代理对并转换，不过这类转义在URL中相对少见。
• 注意区分Unicode转义和标准URL编码（比如%3D）：标准URL编码可以直接用PostgreSQL内置的url_decode()函数处理，但你的场景是uXXXX格式，所以不适用。

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