REGEXP_REPLACE替换管道符时无法去尾的问题排查及替代方案

使用REGEXP_REPLACE拼接字段时，用逗号做分隔符能正常移除尾部逗号，但换成管道符|后，尾部管道符始终无法移除。

逗号分隔的正常实现 #

代码 #

select username,
  regexp_replace (
   (case when clan is not null then clan ||', ' END) ||
   (case when job is not null then job ||', ' END) ||       
   (case when level is not null then level END), ', $',' ') desc      
 from dominions;

正确输出 #

USERNAME       DESC
 benedict       rage, hunter, 89
 jamilah        wizard, 42
 anne           happy_elfs, elven
 tommy          19

管道符分隔的错误实现 #

代码 #

select username,
  regexp_replace (
   (case when clan is not null then clan ||' | ' END) ||
   (case when job is not null then job ||' | ' END) ||       
   (case when level is not null then level END), '| $',' ') desc      
 from dominions;

错误输出 #

USERNAME       DESC
 benedict       rage | hunter | 89 |
 jamilah        wizard | 42 |
 anne           happy_elfs | elven |
 tommy          19 |   

正则表达式里的|是特殊元字符，代表“或”的逻辑判断。你写的'| $'会被解析成“匹配空字符 或 匹配末尾的空格”，完全不会匹配到尾部的| 。必须把|转义成\|，才能让正则识别它是字面意义上的管道符。

select username,
  regexp_replace (
   (case when clan is not null then clan ||' | ' END) ||
   (case when job is not null then job ||' | ' END) ||       
   (case when level is not null then level END), '\| $',' ') desc      
 from dominions;

这样就能正确匹配并移除尾部的| 了。

如果不想纠结正则元字符问题，也可以用非正则的方式实现：

select username,
  TRIM(BOTH ' | ' FROM 
    COALESCE(clan || ' | ', '') ||
    COALESCE(job || ' | ', '') ||
    COALESCE(level, '')
  ) as desc
from dominions;

或者换一种正则写法，先拼接所有可能的内容再统一清理尾部：

select username,
  REGEXP_REPLACE(
    COALESCE(clan || ' | ', '') ||
    COALESCE(job || ' | ', '') ||
    COALESCE(level, ''),
    '\| $', ''
  ) as desc
from dominions;

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