在varchar列中查找非数字值

一尘不染

在varchar列中查找非数字值

sql

要求：

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

可用值：

表名称，列名称，允许的精度，允许的比例

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

我的解决方案：

使用Regexp验证号码 NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$')

验证左部分的长度（十进制前）（我不知道它的实际名称是什么），因为对于小数位数，oracle会在需要时自动四舍五入。由于实际列为varchar，因此我将使用substr，instr在小数点左侧找到组件。

如上所述，Regexp允许使用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，但总体上来说，我的效率非常低下。

我将非常感谢[以任何方式]更好的解决方案。

提前致谢。

阅读 150

2021-03-08

共1个答案

一尘不染

精度意味着您最多需要allowed_precision数字中的数字（严格来说，不计算前导零，但我会忽略这一点）。刻度表示最多allowed_scale可以在小数点后。

这建议使用正则表达式，例如：

[-]?[0-9]{1,<before>}[.]?[0-9]{0,<after>}

您可以构造正则表达式：

NOT REGEXP_LIKE(COLUMN_NAME,
                REPLACE(REPLACE('[-]?[0-9]{1,<before>}[.]?[0-9]{0,<after>}', '<before>', allowed_precision - allowed_scale
                               ), '<after>', allowed_scale)

现在，变量正则表达式的效率非常低。您也可以使用like和其他功能来执行逻辑。我认为条件是：

(column_name not like '%.%.%' and
 column_name not like '_%-%' and
 translate(column_name, '0123456789-.x', 'x') is null and
 length(translate(column_name, '-.x', 'x') <= allowed_precision and
 length(translate(column_name, '-.x', 'x') >= 1 and
 instr(translate(column_name, '-.x', 'x'), '.') <= allowed_precision - allowed_scale
)

2021-03-08