检查 Oracle 中的“它是一个数字”函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5082176/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
check if "it's a number" function in Oracle
提问by Fgblanch
I'm trying to check if a value from a column in an oracle (10g) query is a number in order to compare it. Something like:
我正在尝试检查 oracle (10g) 查询中列中的值是否为数字以便进行比较。就像是：
select case when ( is_number(myTable.id) and (myTable.id >0) )
then 'Is a number greater than 0'
else 'it is not a number'
end as valuetype
from table myTable
Any ideas on how to check that?
关于如何检查的任何想法？
采纳答案by Justin Cave
Assuming that the ID column in myTableis not declared as a NUMBER (which seems like an odd choice and likely to be problematic), you can write a function that tries to convert the (presumably VARCHAR2) ID to a number, catches the exception, and returns a 'Y' or an 'N'. Something like
假设 ID 列myTable未声明为 NUMBER（这似乎是一个奇怪的选择并且可能有问题），您可以编写一个函数来尝试将（大概是 VARCHAR2）ID 转换为数字，捕获异常，然后返回“Y”或“N”。就像是
CREATE OR REPLACE FUNCTION is_number( p_str IN VARCHAR2 )
RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
IS
l_num NUMBER;
BEGIN
l_num := to_number( p_str );
RETURN 'Y';
EXCEPTION
WHEN value_error THEN
RETURN 'N';
END is_number;
You can then embed that call in a query, i.e.
然后您可以将该调用嵌入到查询中，即
SELECT (CASE WHEN is_number( myTable.id ) = 'Y' AND myTable.id > 0
THEN 'Number > 0'
ELSE 'Something else'
END) some_alias
FROM myTable
Note that although PL/SQL has a boolean data type, SQL does not. So while you can declare a function that returns a boolean, you cannot use such a function in a SQL query.
请注意，尽管 PL/SQL 具有布尔数据类型，但 SQL 没有。因此，虽然您可以声明一个返回布尔值的函数，但您不能在 SQL 查询中使用这样的函数。
回答by Saish
One additional idea, mentioned hereis to use a regular expression to check:
这里提到的另一个想法是使用正则表达式来检查：
SELECT foo
FROM bar
WHERE REGEXP_LIKE (foo,'^[[:digit:]]+$');
The nice part is you do not need a separate PL/SQL function. The potentially problematic part is that a regular expression may not be the most efficient method for a large number of rows.
好的部分是您不需要单独的 PL/SQL 函数。潜在的问题部分是正则表达式可能不是处理大量行的最有效方法。
回答by Matt Byrne
Saish's answer using REGEXP_LIKEis the right idea but does not support floating numbers. This one will ...
Saish 的答案 usingREGEXP_LIKE是正确的想法，但不支持浮点数。这个会...
Return values that are numeric
返回数字值
SELECT foo
FROM bar
WHERE REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');
Return values not numeric
返回值不是数字
SELECT foo
FROM bar
WHERE NOT REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');
You can test your regular expressions themselves till your heart is content at http://regexpal.com/(but make sure you select the checkbox match at line breaksfor this one).
您可以在http://regexpal.com/ 上测试您的正则表达式，直到您满意为止 （但请确保您选择了在换行符处与此匹配的复选框）。
回答by kevlened
This is a potential duplicate of Finding rows that don't contain numeric data in Oracle. Also see: How can I determine if a string is numeric in SQL?.
这是在 Oracle中查找不包含数字数据的行的潜在重复项。另请参阅：如何确定 SQL 中的字符串是否为数字？.
Here's a solution based on Michael Durrant'sthat works for integers.
这是基于Michael Durrant 的适用于整数的解决方案。
SELECT foo
FROM bar
WHERE DECODE(TRIM(TRANSLATE(your_number,'0123456789',' ')), NULL, 'number','contains char') = 'number'
Adrian Carneiroposted a solution that works for decimals and others. However, as Justin Cave pointed out, this will incorrectly classify strings like '123.45.23.234' or '131+234'.
Adrian Carneiro发布了一个适用于小数和其他的解决方案。但是，正如 Justin Cave 指出的那样，这会错误地对诸如“123.45.23.234”或“131+234”之类的字符串进行分类。
SELECT foo
FROM bar
WHERE DECODE(TRIM(TRANSLATE(your_number,'+-.0123456789',' ')), NULL, 'number','contains char') = 'number'
If you need a solution without PL/SQL or REGEXP_LIKE, this may help.
如果您需要没有 PL/SQL 或 REGEXP_LIKE 的解决方案，这可能会有所帮助。
回答by Kanu
You can use the regular expression function 'regexp_like' in ORACLE (10g)as below:
您可以在 ORACLE (10g) 中使用正则表达式函数“regexp_like”，如下所示：
select case
when regexp_like(myTable.id, '[[:digit:]]') then
case
when myTable.id > 0 then
'Is a number greater than 0'
else
'Is a number less than or equal to 0'
end else 'it is not a number' end as valuetype
from table myTable
回答by Filipe Silva
I'm against using when othersso I would use (returning an "boolean integer" due to SQL not suppporting booleans)
我反对使用when others所以我会使用（由于 SQL 不支持布尔值而返回一个“布尔整数”）
create or replace function is_number(param in varchar2) return integer
is
ret number;
begin
ret := to_number(param);
return 1; --true
exception
when invalid_number then return 0;
end;
In the SQL call you would use something like
在 SQL 调用中，您将使用类似
select case when ( is_number(myTable.id)=1 and (myTable.id >'0') )
then 'Is a number greater than 0'
else 'it is not a number or is not greater than 0'
end as valuetype
from table myTable
回答by TheBakker
This is my query to find all those that are NOT number :
这是我查找所有非数字的查询：
Select myVarcharField
From myTable
where not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\.\d+)?$', '')
and not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\,\d+)?$', '');
In my field I've . and , decimal numbers sadly so had to take that into account, else you only need one of the restriction.
在我的领域，我已经。和，可悲的是十进制数，所以必须考虑到这一点，否则你只需要一个限制。
回答by Pancho
@JustinCave - The "when value_error" replacement for "when others" is a nice refinement to your approach above. This slight additional tweak, while conceptually the same, removes the requirement for the definition of and consequent memory allocation to your l_num variable:
@JustinCave - “when value_error”替换“when others”是对上述方法的一个很好的改进。这个轻微的额外调整，虽然在概念上是相同的，但消除了对 l_num 变量的定义和随后的内存分配的要求：
function validNumber(vSomeValue IN varchar2)
return varchar2 DETERMINISTIC PARALLEL_ENABLE
is
begin
return case when abs(vSomeValue) >= 0 then 'T' end;
exception
when value_error then
return 'F';
end;
Just a note also to anyone preferring to emulate Oracle number format logic using the "riskier" REGEXP approach, please don't forget to consider NLS_NUMERIC_CHARACTERS and NLS_TERRITORY.
对于喜欢使用“风险更高”的 REGEXP 方法模拟 Oracle 数字格式逻辑的任何人，请不要忘记考虑 NLS_NUMERIC_CHARACTERS 和 NLS_TERRITORY。
回答by Waqas Ashraf
CREATE OR REPLACE FUNCTION is_number(N IN VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN CASE regexp_like(N,'^[\+\-]?[0-9]*\.?[0-9]+$') WHEN TRUE THEN 1 ELSE 0 END;
END is_number;
Please note that it won't consider 45e4 as a number, But you can always change regex to accomplish the opposite.
请注意，它不会将 45e4 视为数字，但您始终可以更改正则表达式以实现相反的目的。
回答by tbone
How is the column defined? If its a varchar field, then its not a number (or stored as one). Oracle may be able to do the conversion for you (eg, select * from someTable where charField = 0), but it will only return rows where the conversion holds true and is possible. This is also far from ideal situation performance wise.
列是如何定义的？如果它是一个 varchar 字段，那么它不是一个数字（或存储为一个）。Oracle 可能能够为您进行转换（例如， select * from someTable where charField = 0），但它只会返回转换成立且可能的行。这也远非理想情况下的表现明智。
So, if you want to do number comparisons and treat this column as a number, perhaps it should be defined as a number?
那么，如果要进行数字比较并将此列视为数字，也许应该将其定义为数字？
That said, here's what you might do:
也就是说，您可以这样做：
create or replace function myToNumber(i_val in varchar2) return number is
v_num number;
begin
begin
select to_number(i_val) into v_num from dual;
exception
when invalid_number then
return null;
end;
return v_num;
end;
You might also include the other parameters that the regular to_number has. Use as so:
您还可以包括常规 to_number 具有的其他参数。像这样使用：
select * from someTable where myToNumber(someCharField) > 0;
It won't return any rows that Oracle sees as an invalid number.
它不会返回任何被 Oracle 视为无效数字的行。
Cheers.
干杯。
