TRANSLATE()函数的大小写敏感性及优先级问题

你遇到的问题根源是数据库默认排序规则不区分大小写，导致TRANSLATE在匹配时会将大小写字符视为等价，进而出现不符合预期的替换结果。以下是几种可行的解决办法：

方法1：指定区分大小写的排序规则 #

在TRANSLATE的参数中显式指定区分大小写的排序规则，让函数严格按照字符大小写进行匹配。不同数据库的规则名称有所不同，以下是常见数据库的示例：

-- SQL Server 示例
SELECT TRANSLATE('NEE' COLLATE Latin1_General_CS_AS, 'eE' COLLATE Latin1_General_CS_AS, 'eE' COLLATE Latin1_General_CS_AS)
-- 输出：NEE

SELECT TRANSLATE('NÉE' COLLATE Latin1_General_CS_AS, 'éÉ' COLLATE Latin1_General_CS_AS, 'eE' COLLATE Latin1_General_CS_AS)
-- 输出：NEE

-- MySQL 示例
SELECT TRANSLATE('NEE' COLLATE utf8_bin, 'eE' COLLATE utf8_bin, 'eE' COLLATE utf8_bin);
-- 输出：NEE

-- PostgreSQL 示例
SELECT TRANSLATE('NEE' COLLATE "C", 'eE' COLLATE "C", 'eE' COLLATE "C");
-- 输出：NEE

方法2：用嵌套REPLACE替代TRANSLATE #

如果替换逻辑简单，或者数据库不支持灵活指定排序规则，可以用嵌套的REPLACE函数分别处理每个大小写字符，确保替换规则完全独立：

-- 替代TRANSLATE('NEE', 'eE', 'eE')，严格区分大小写替换
SELECT REPLACE(REPLACE('NEE', 'E', 'E'), 'e', 'e')
-- 输出：NEE

-- 带重音字符的处理
SELECT REPLACE(REPLACE('NÉE', 'É', 'E'), 'é', 'e')
-- 输出：NEE

方法3：使用二进制比较 #

将字符串转换为二进制形式后再执行TRANSLATE，利用大小写字符二进制编码不同的特性实现严格匹配。以MySQL为例：

SELECT TRANSLATE(BINARY 'NEE', BINARY 'eE', BINARY 'eE')
-- 输出：NEE

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