oracle 甲骨文。将 varchar 值“40.00”转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1636190/
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
Oracle. Convert varchar value "40.00" to numerical
提问by Max
For example, a have varchar value "40.00" and want to use it with operators ">" or "<" in where clause. How can I use it?
例如，具有 varchar 值“40.00”并希望在 where 子句中与运算符“>”或“<”一起使用它。我怎样才能使用它？
回答by Andomar
You could convert the varchar to a decimal like:
您可以将 varchar 转换为十进制，如：
select *
from YourTable
where CAST(YourField AS decimal) < 40.00
Or use the TO_NUMBER()function:
或者使用TO_NUMBER()函数：
select *
from YourTable
where TO_NUMBER(YourField) < 40.00
If the field is not always a number, and you have a relatively recent Oracle installation, you can select rows where YourField is numeric like:
如果该字段不总是数字，并且您的 Oracle 安装相对较新，则可以选择 YourField 为数字的行，例如：
select *
from (
select *
from YourTable
where regexp_like(YourField, '^-?[[:digit:],.]+$')
) sub
where TO_NUMBER(YourField) < 40.00
回答by Tony Andrews
You can convert it to a number using the TO_NUMBER function:
您可以使用 TO_NUMBER 函数将其转换为数字：
WHERE TO_NUMBER(col) > 39
But beware: if any row in the table has a non-numeric entry in that column, the query may fail with an error:
但请注意：如果表中的任何行在该列中有非数字条目，则查询可能会失败并显示错误：
SQL> create table t (col varchar2(10));
Table created.
SQL> insert into t values ('39.00');
1 row created.
SQL> insert into t values ('40.00');
1 row created.
SQL> insert into t values ('41.00');
1 row created.
SQL> insert into t values ('xxx');
1 row created.
SQL> select * from t where to_number(col) > 39;
ERROR:
ORA-01722: invalid number
回答by user151019
If you want to compare them as numbers then use the TO_NUMBER function Oracle 11g doc
如果要将它们作为数字进行比较，请使用 TO_NUMBER 函数Oracle 11g doc
I would suggest that if you want to treat thus as a number the best way is to change your schema/DDL so that the 40.00 is stored in a number field. This would mean that comparisons work , access be more efficient e.g. where field > x would use an index - where TO_NUMBER(field) > x does not use an index and data errors can be caught on insert rather than having to run reports to find non numeric data.
我建议，如果您想将其视为数字，最好的方法是更改架构/DDL，以便将 40.00 存储在数字字段中。这意味着比较有效，访问效率更高，例如，field > x 将使用索引 - 其中 TO_NUMBER(field) > x 不使用索引，并且可以在插入时捕获数据错误，而不必运行报告来查找非数字数据。
回答by Patrikoko
AND (TO_NUMBER(NVL(NAME_FIELD,'0'),'99999999D999','nls_numeric_characters=,.') > 0)
