PostgreSQL中SIMILAR TO模式匹配无结果问题求助

问题原因 #

你的field_name列定义为CHAR(10)，PostgreSQL会自动将存储的字符串用空格填充至10个字符长度。比如你看到的200，实际存储值是200加上7个空格，总长度为10。

第一个查询中2\[0-9\]\[0-9\]%的%表示匹配模式后可跟随任意字符（包括空格），所以能命中结果；而第二个查询[23][0-9][0-2]要求整个字符串完全匹配3个字符的模式，但实际字符串是10个字符（含空格），因此无结果返回。

解决方法 #

以下几种方案都可以解决这个问题：

• 方案1：使用trim()去除末尾空格后匹配
  先去掉字段末尾的填充空格，再进行模式匹配：

  select distinct field_name, length(field_name) 
  from table_a 
  where trim(field_name) similar to '[23][0-9][0-2]';
  

• 方案2：修改匹配模式兼容空格填充
  明确匹配开头3个符合规则的字符，后续为任意数量的空格（因为CHAR(10)固定长度，也可以精确匹配7个空格）：

  -- 兼容任意数量后续字符（适合不确定填充长度的场景）
  select distinct field_name, length(field_name) 
  from table_a 
  where field_name similar to '[23][0-9][0-2]%' 
    and char_length(field_name) = 10;
  
  -- 精确匹配10个字符（3个目标字符+7个空格）
  select distinct field_name, length(field_name) 
  from table_a 
  where field_name similar to '[23][0-9][0-2]' || repeat(' ', 7);
  

• 方案3：提取前3个字符进行匹配
  直接截取字段的前3个字符，只对这部分进行模式校验：

  select distinct field_name, length(field_name) 
  from table_a 
  where substring(field_name from 1 for 3) similar to '[23][0-9][0-2]';
  

内容的提问来源于stack exchange，提问作者M Remus Z