Oracle SQL中筛选VARCHAR列数值小于100的行的实现方法

你的问题出在直接用TO_NUMBER(value)的时候，列里的非数值字符串（比如aa、cc）会触发转换错误，导致整个查询失败。得先把这些非数字的行过滤掉，再进行数值比较。下面分几种常见数据库给你解决方案：

Oracle数据库 #

方法1：用正则表达式过滤纯数字行 #

适合所有Oracle版本，通过REGEXP_LIKE先匹配纯数字的字符串，再转换比较：

SELECT * 
FROM mytable 
WHERE REGEXP_LIKE(value, '^[0-9]+$') -- 匹配正整数，有小数的话改成'^[0-9]+(\.[0-9]+)?$'
  AND TO_NUMBER(value) < 100;

方法2：用VALIDATE_CONVERSION（Oracle 12c+） #

Oracle 12c及以上提供了更直观的验证函数，返回1表示可以转换为数字：

SELECT * 
FROM mytable 
WHERE VALIDATE_CONVERSION(value AS NUMBER) = 1
  AND TO_NUMBER(value) < 100;

MySQL数据库 #

MySQL里可以用正则先过滤，再用CAST或CONVERT转换：

SELECT * 
FROM mytable 
WHERE value REGEXP '^[0-9]+$'
  AND CAST(value AS UNSIGNED) < 100;

如果需要支持小数，把正则改成'^[0-9]+(\.[0-9]+)?$'，转换类型改成DECIMAL即可。

SQL Server数据库 #

SQL Server的TRY_CAST/TRY_CONVERT会在转换失败时返回NULL，利用这个特性过滤：

SELECT * 
FROM mytable 
WHERE TRY_CAST(value AS INT) IS NOT NULL
  AND TRY_CAST(value AS INT) < 100;

如果要支持小数，把INT换成DECIMAL(10,2)这类类型。

核心思路就是先排除无法转换为数字的行，再对合法的数字字符串做转换和比较，这样就不会出现转换错误了。

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