如何在Select查询中忽略精确匹配的两位前导零？

该需求可以完全通过原生SQL实现，无需应用层遍历全表，也无需使用LIKE运算符，不会返回无关匹配结果。

实现思路 #

核心是对数据库存储的refNumber字段、传入的查询参数做相同的标准化处理：如果字符串开头恰好是两位前导零，则移除这两位零，否则保留原字符串。将两者标准化后的结果做等值匹配即可满足需求。

各数据库实现示例 #

以下示例统一用预编译参数占位符代表传入的查询值，实际使用时替换为对应数据库的参数绑定语法即可。

MySQL #

SELECT id, refNumber
FROM 你的表名
WHERE 
  IF(LEFT(refNumber, 2) = '00', SUBSTRING(refNumber, 3), refNumber)
  = 
  IF(LEFT(?, 2) = '00', SUBSTRING(?, 3), ?);

PostgreSQL #

SELECT id, refNumber
FROM 你的表名
WHERE
  CASE WHEN LEFT(refNumber, 2) = '00' THEN SUBSTRING(refNumber FROM 3) ELSE refNumber END
  =
  CASE WHEN LEFT($1, 2) = '00' THEN SUBSTRING($1 FROM 3) ELSE $1 END;

SQL Server #

SELECT id, refNumber
FROM 你的表名
WHERE
  CASE WHEN LEFT(refNumber, 2) = '00' THEN SUBSTRING(refNumber, 3, LEN(refNumber)) ELSE refNumber END
  =
  CASE WHEN LEFT(@queryParam, 2) = '00' THEN SUBSTRING(@queryParam, 3, LEN(@queryParam)) ELSE @queryParam END;

性能优化方案 #

如果数据量较大，每次查询时的实时计算会影响性能，可以新增标准化计算列并加索引，查询性能和普通等值查询一致：
以MySQL为例：

-- 新增持久化的标准化计算列
ALTER TABLE 你的表名 
ADD COLUMN norm_refNumber VARCHAR(255) 
GENERATED ALWAYS AS (IF(LEFT(refNumber, 2) = '00', SUBSTRING(refNumber, 3), refNumber)) STORED;

-- 加索引优化查询
CREATE INDEX idx_norm_ref ON 你的表名(norm_refNumber);

优化后的查询语句：

SELECT id, refNumber
FROM 你的表名
WHERE norm_refNumber = IF(LEFT(?, 2) = '00', SUBSTRING(?, 3), ?);

边界说明 #

• 该逻辑严格遵循「仅匹配两位前导零」的要求：如果字段或查询参数的前导零超过两位（例如000123），只会移除前两位，剩余的前导零会保留参与匹配，不会出现误匹配。
• 如果业务要求refNumber长度不小于3位，可以提前加参数校验，避免处理长度为2的00值时返回空字符串的情况。

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