SQL如何查询varchar类型tranche区间列中包含指定数值的行

解决方案 #

你可以通过拆分tranche字段的左右区间边界，再和输入值做数值比较实现需求，通用实现逻辑如下：

1. 用字符串拆分函数把tranche字段按-分割为左右两个边界字符串
2. 把拆分得到的两个字符串转为数值类型，避免字符串比较的前缀匹配误差
3. 筛选输入值大于等于左边界、且小于等于右边界的行即可

MySQL 示例代码

假设表名为your_table，输入的数值为@input_num：

SELECT * 
FROM your_table
WHERE @input_num BETWEEN 
  CAST(SUBSTRING_INDEX(tranche, '-', 1) AS UNSIGNED) 
  AND 
  CAST(SUBSTRING_INDEX(tranche, '-', -1) AS UNSIGNED);

PostgreSQL 示例代码

SELECT *
FROM your_table
WHERE :input_num BETWEEN 
  split_part(tranche, '-', 1)::INTEGER
  AND
  split_part(tranche, '-', 2)::INTEGER;

SQL Server 示例代码

SELECT *
FROM your_table
WHERE @input_num BETWEEN
  CAST(LEFT(tranche, CHARINDEX('-', tranche)-1) AS INT)
  AND
  CAST(RIGHT(tranche, LEN(tranche) - CHARINDEX('-', tranche)) AS INT);

注意事项 #

• 如果tranche字段存在非XX-XX格式的脏数据，建议先增加格式校验过滤条件，避免类型转换报错
• 如果该查询使用频率很高，建议单独新增左边界、右边界两个数值类型的预计算字段并加索引，避免每次查询都做字符串拆分和类型转换，大幅提升查询性能

内容的提问来源于stack exchange，提问作者Michel Gisenaël