在 varchar 列中查找非数字值

要求 ：

通用查询/函数，用于检查表中 varchar 列中提供的值是否实际上是数字且精度不超过允许的精度。

可用值：

表名称、列名称、允许的精度、允许的小数位数

一般建议是创建一个函数并使用 to_number() 来验证该值，但它不会验证允许的长度（精度范围）。

我的解决方案：

使用正则表达式验证数字NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$')

验证左侧组件的长度（小数点之前）（我不知道它的实际名称是什么），因为对于比例，oracle 如果需要，会自动四舍五入。由于实际列是 varchar 我将使用 substr, instr 来查找小数点左侧的组件。

如上所述，正则表达式允许像 123...123124..55 这样的数字，我还将验证小数点的数量。 [如果 > 1 则错误]

查询查找无效号码：

Select * From Table_Name 
Where
(NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$')
OR
Function_To_Fetch_Left_Component(COLUMN_NAME) > (Precision-Scale)
/* Can use regexp_substr now but i already had a function for that */
OR
LENGTH(Column_Name) - LENGTH(REPLACE(Column_Name,'.','')) > 1
/* Can use regexp_count aswell*/)

我对我的解决方案感到高兴和满意，直到一列只有“.”。价值逃过了我的支票，我看到了我的支票的局限性。虽然添加另一个检查来验证这一点也可以解决我的问题solution总的来说，我觉得效率很低。

我真的很感激[以任何方式]更好的解决方案。

提前致谢。

寻找：

• 一位或多位数字（可选）后跟小数点和零位或多位数字；或者
• 前导小数点（前面无单位数字），然后是一位或多位（十进制）数字。

像这样：

Select *
From   Table_Name 
Where  NOT REGEXP_LIKE(COLUMN_NAME, '^[+-]?(\d+(\.\d*)?|\.\d+)$')

如果您不想在数字字符串中使用零填充值，则：

Select *
From   Table_Name 
Where  NOT REGEXP_LIKE(COLUMN_NAME, '^[+-]?(([1-9]\d*|0)(\.\d*)?|\.\d+)$')

具有精度和规模（假设它按照NUMBER( precision, scale )数据类型和scale < precision):

Select *
From   Table_Name 
Where  NOT REGEXP_LIKE(COLUMN_NAME, '^[+-]?(\d{1,'||(precision-scale)||'}(\.\d{0,'||scale||'})?|\.\d{1,'||scale||'})$')

或者，对于具有精度和小数位数的非零填充数字：

Select *
From   Table_Name 
Where  NOT REGEXP_LIKE(COLUMN_NAME, '^[+-]?(([1-9]\d{0,'||(precision-scale-1)||'}|0)(\.\d{0,'||scale||'})?|\.\d{1,'||scale||'})$')

或者，对于任何精度和比例：

Select *
From   Table_Name 
Where  NOT REGEXP_LIKE(
             COLUMN_NAME,
             CASE
               WHEN scale <= 0
               THEN '^[+-]?(\d{1,'||precision||'}0{'||(-scale)||'})$'
               WHEN scale < precision
               THEN '^[+-]?(\d{1,'||(precision-scale)||'}(\.\d{0,'||scale||'})?|\.\d{1,'||scale||'})$'
               WHEN scale >= precision
               THEN '^[+-]?(0(\.0{0,'||scale||'})?|0?\.0{'||(scale-precision)||'}\d{1,'||precision||'})$'
             END
           )