从SQL Server迁移至Postgres：字符有效性校验问题排查

你的代码存在两个核心问题，导致Ä这类重音字符未被判定为无效：

1. 逻辑完全颠倒 #

当前CASE WHEN的条件是匹配不在有效集合内的字符时保留原字符，这与你“保留有效字符、替换无效字符”的需求完全相反。SQL Server的原逻辑应该是仅保留符合规则的字符，而你现在的Postgres代码刚好写反了判断条件。

2. 正则表达式写法错误 #

• 你使用similar to的[^...]负向字符类，本意是定义有效字符的排除集，但后续又额外排除[、]、^，逻辑混乱；
• 字符类中的-没有放在开头或结尾，会被当作范围符号（比如]--"会被解析为从]到"的字符范围），导致正则匹配不符合预期；
• HTML转义后的引号（"）在Postgres中应使用双引号转义（即""），但你的写法冗余且易出错。

修正后的代码 #

直接使用Postgres的regexp_replace函数可以更高效地实现需求，无需逐字符遍历：

SELECT regexp_replace('ÄZYasdXA', '[^a-zA-Z0-9_{}"() *&%$#@!?/\;:,.—<>–+ =`|~-]', '|', 'g') AS stringagg;

代码说明 #

• [^a-zA-Z0-9_{}"() *&%$#@!?/\;:,.—<>–+ =|~-]`：定义无效字符集合（即所有不在有效范围内的字符），其中：
  • a-zA-Z0-9匹配ASCII大小写字母和数字；
  • 后续的特殊字符是你允许的符号，-放在字符类末尾避免被当作范围符号；
• 'g'参数表示全局替换，将所有无效字符替换为|；
• 该正则不会匹配Ä这类Unicode重音字符，因此会被替换为|，与SQL Server的行为一致。

如果你坚持要保留原逐字符遍历的写法，修正后的CASE WHEN逻辑如下：

SELECT
  string_agg(txml.substr_text, '') AS stringagg
FROM (
  SELECT
    CASE
      WHEN substring('ÄZYasdXA', n, 1) SIMILAR TO '[a-zA-Z0-9_{}"() *&%$#@!?/\;:,.—<>–+ =`|~-]'
      THEN substring('ÄZYasdXA', n, 1)
      ELSE '|'
    END AS substr_text
  FROM (SELECT * FROM generate_series(1, 1000)) AS nums (n)
  WHERE n <= LENGTH('ÄZYasdXA')
) AS txml;

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