PostgreSQL 14中如何移除字符串首尾各一个单引号

问题场景 #

在PostgreSQL 14环境下，需要移除字符串首尾各一个单引号。

原始字符串 #

'''alter table test add column col2 int[] default ''''{}'''' not null'''

期望输出结果 #

''alter table test add column col2 int[] default ''''{}'''' not null''

尝试的方法及问题 #

使用trim()函数处理时，该函数会同时移除中间大括号两侧的转义单引号，执行语句及结果如下：

=# select trim('''alter table test add column col2 int[] default ''''{}'''' not null''');
                              btrim
------------------------------------------------------------------
 'alter table test add column col2 int[] default ''{}'' not null'

解决方案 #

trim()函数的核心问题是：它会移除字符串首尾所有连续的指定字符（默认是空白字符），而我们需要的是仅移除首尾各一个单引号，保留中间的转义单引号。

方法1：使用substr()精准截取 #

通过截取字符串的第2个字符到倒数第2个字符，实现移除首尾各一个字符：

select substr('''alter table test add column col2 int[] default ''''{}'''' not null''', 2, length('''alter table test add column col2 int[] default ''''{}'''' not null''') - 2);

执行后得到的结果与期望一致：

''alter table test add column col2 int[] default ''''{}'''' not null''

方法2：使用正则表达式匹配 #

利用substring的正则匹配功能，精准捕获首尾单引号之间的内容：

select substring('''alter table test add column col2 int[] default ''''{}'''' not null''' from '''(.*)''');

该正则会匹配开头的一个单引号，捕获中间所有内容，再匹配结尾的一个单引号，最终返回捕获的中间部分，自动保留原有的转义单引号。

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