如何在PostgreSQL中使用RLIKE正则实现大小写敏感查询

解决方案 #

PostgreSQL原生支持大小写敏感的正则匹配，你可以直接使用以下几种方案实现需求：

• 方案1：使用原生大小写敏感正则运算符~
  PostgreSQL中~为默认大小写敏感的正则匹配运算符，大小写不敏感匹配才需要用~*，你之前使用的RLIKE如果是适配其他数据库的兼容语法，大概率默认映射到了大小写不敏感的匹配规则，替换为原生运算符即可：

SELECT
    CITY
FROM
    TARGETS
WHERE
    CITY ~ '^b.*n$';

• 方案2：指定大小写敏感排序规则
  如果你的数据库/表/字段默认设置了大小写不敏感的排序规则（collate），可以在查询时临时指定大小写敏感的"C"排序规则：

SELECT
    CITY
FROM
    TARGETS
WHERE
    CITY COLLATE "C" ~ '^b.*n$';

• 方案3：增加全小写校验
  如果需要额外确保字段值全为小写，可以叠加一层原值与转小写后的值相等的判断：

SELECT
    CITY
FROM
    TARGETS
WHERE
    LOWER(CITY) = CITY
    AND CITY ~ '^b.*n$';

说明 #

你之前查询到的CAST(CITY AS BINARY)是MySQL的专属语法，PostgreSQL没有对应实现，也不需要用转二进制的方式实现大小写敏感匹配，使用上述原生语法即可。

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