如何用SQL查询USER_TABLE中包含指定数值的范围数据？

USER_TABLE表的USERS_RANGE字段存储着类似“5-98”的范围值，需求是：当搜索指定数值（比如7）时，返回所有该数值落在其范围内的记录（例如搜索7时，要返回包含“5-98”“1-20”的记录）。试过select * from USER_TABLE WHERE USERS_RANGE <= '7';以及在Java里做数学处理，想找合适的SQL查询语句直接实现这个需求。

核心思路是把字符串形式的范围拆成最小值和最大值，再做数值比较。不同数据库的字符串处理函数有差异，以下是几种常用数据库的实现：

MySQL/MariaDB #

用SUBSTRING_INDEX拆分范围字符串，转成数值后做区间判断：

SELECT * 
FROM USER_TABLE 
WHERE 
  CAST(SUBSTRING_INDEX(USERS_RANGE, '-', 1) AS UNSIGNED) <= 7 
  AND CAST(SUBSTRING_INDEX(USERS_RANGE, '-', -1) AS UNSIGNED) >= 7;

把语句里的7换成你要搜索的目标数值即可。

PostgreSQL #

借助STRING_TO_ARRAY拆分字符串，取数组元素转成整数后比较：

SELECT * 
FROM USER_TABLE 
WHERE 
  (STRING_TO_ARRAY(USERS_RANGE, '-'))[1]::INT <= 7 
  AND (STRING_TO_ARRAY(USERS_RANGE, '-'))[2]::INT >= 7;

SQL Server #

用CHARINDEX定位分隔符位置，截取前后子串转成数值后判断：

SELECT * 
FROM USER_TABLE 
WHERE 
  CAST(SUBSTRING(USERS_RANGE, 1, CHARINDEX('-', USERS_RANGE) - 1) AS INT) <= 7 
  AND CAST(SUBSTRING(USERS_RANGE, CHARINDEX('-', USERS_RANGE) + 1, LEN(USERS_RANGE)) AS INT) >= 7;

• 要是USERS_RANGE字段存在格式不规范的数据（比如没带-、包含非数字内容），最好先加过滤条件避免转换报错，比如可以追加WHERE USERS_RANGE LIKE '%-%'筛掉不合格的记录。
• 如果数据量较大，这种字符串拆分转换的查询无法利用索引，速度会很慢。建议直接把范围拆成两个单独的字段（比如min_range和max_range），给这两个字段建索引后，查询效率会大幅提升。

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