在varchar列中查找非数字值 [英] Finding non-numeric values in varchar column

问题描述

要求:

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

Generic query/function to check if the value provided in a varchar column in a table is actually a number & the precision does not exceed the allowed precision.

可用值:

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

Table_Name, Column_Name, Allowed Precision, Allowed Scale

一般建议是创建一个功能&使用to_number()验证值，但不会验证允许的长度(精度标度).

General advise would be to create a function & use to_number() to validate the value however it won't validate the allowed length (precision-scale).

我的解决方案:

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

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

Validate Length of left component (before decimal) (I have no idea what's its actually called) because for scale, oracle automatically rounds off if required. As the actual column is varchar i will use substr, instr to find the component on the left of decimal point.

如上所述，Regexp允许使用123 ... 123124..55这样的数字，我还将验证小数点的数量. [如果> 1则错误]

As above Regexp allows number like 123...123124..55 I will also validate the number of decimal points. [If > 1 then error]

查询以查找无效号码:

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对我而言似乎效率很低.

---

I was happy & satisfied with my solution until a column with only '.' value escaped my check and I saw the limitation of my checks. Although adding another check to validate this as well will solve my problem the solution as a whole looks very inefficient to me.

我真的很乐意以任何方式提供更好的解决方案.

I will really appreciate a better solution [in any way].

谢谢.

推荐答案

精度意味着您最多希望数字中的allowed_precision个数字(严格来说，不算前导零，但我会忽略它).标度表示最多allowed_scale可以在小数点后.

The precision means that you want at most allowed_precision digits in the number (strictly speaking, not counting leading zeros, but I'll ignore that). The scale means that at most allowed_scale can be after the decimal point.

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

This suggests a regular expression such as:

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

您可以构造正则表达式:

You can construct the regular expression:

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

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

Now, variable regular expressions are highly inefficient. You can do the logic using like and other functions as well. I think the conditions are:

(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
)

这篇关于在varchar列中查找非数字值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！