PostgreSQL TRIM函数无法正确截取字符串问题求助

你用错PostgreSQL的TRIM函数了！TRIM(string, characters)的作用不是移除固定前缀字符串，而是移除字符串两端所有属于指定字符集合的字符——只要字符在第二个参数的字符列表里，就会被从开头和结尾逐个删掉，直到遇到不在集合里的字符为止。

你的SQL里第二个参数是'//testamplify.amazonaws.co/public/restaurant/'，这个字符串拆成单个字符后，会形成一个包含/、t、e、s、a、m、p、l、i、f、y、.、z、o、n、c、u、b、r、n等字符的集合。TRIM会把原字符串开头所有属于这个集合的字符都删掉，同时也会删掉结尾所有属于这个集合的字符。你得到的结果是'g'，说明原字符串里除了'g'之外的所有字符都在这个字符集合里，所以被全部移除了。

正确解法 #

方法1：用正则表达式替换固定前缀 #

用regexp_replace匹配开头的指定前缀，将其替换为空字符串（注意正则里的.需要用\\转义，因为.在正则中是通配符）：

select 
  regexp_replace(image_url, '^//testamplify\\.amazonaws\\.co/public/restaurant/', '', '') as trimmed_url,
  image_url 
from scimage s 
where ref_id isnull and ref_type = '1';

方法2：用substring截取前缀后的部分 #

如果前缀是固定长度的字符串，可以先计算前缀长度，再从对应位置开始截取：

select 
  substring(image_url from length('//testamplify.amazonaws.co/public/restaurant/') + 1) as trimmed_url,
  image_url 
from scimage s 
where ref_id isnull and ref_type = '1';

也可以用正则形式的substring直接提取前缀后的内容：

select 
  substring(image_url from '^//testamplify\\.amazonaws\\.co/public/restaurant/(.*)') as trimmed_url,
  image_url 
from scimage s 
where ref_id isnull and ref_type = '1';

方法3：用split_part分割字符串 #

利用前缀末尾的restaurant/作为分隔符，取分割后的第二部分：

select 
  split_part(image_url, 'restaurant/', 2) as trimmed_url,
  image_url 
from scimage s 
where ref_id isnull and ref_type = '1';

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