oracle “安全”TO_NUMBER()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4486949/
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
"Safe" TO_NUMBER()
提问by zerkms
SELECT TO_NUMBER('*') FROM DUAL
This obviously gives me an exception:
这显然给了我一个例外：
ORA-01722: invalid number
ORA-01722: 无效号码
Is there a way to "skip" it and get 0or NULLinstead?
有没有办法“跳过”它并获得0或NULL代替？
The whole issue: I have NVARCHAR2field, which contains numbers and not almost ;-) (like *) and I need to select the biggest number from the column.
整个问题：我有一个NVARCHAR2字段，其中包含数字而不是几乎 ;- *) （如），我需要从列中选择最大的数字。
Yes, I know it is a terrible design, but this is what I need now... :-S
是的，我知道这是一个糟糕的设计，但这就是我现在需要的...... :-S
UPD:
更新：
For myself I've solved this issue with
对于我自己，我已经解决了这个问题
COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)
采纳答案by Gabe
I couldn't find anything better than this:
我找不到比这更好的了：
function safe_to_number(p varchar2) return number is
v number;
begin
v := to_number(p);
return v;
exception when others then return 0;
end;
回答by pweitzman
COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+(\.\d+)?')), 0)
will also get numbers with scale > 0 (digits to the right of the decimal point).
还将获得比例 > 0 的数字（小数点右侧的数字）。
回答by Lukasz Szozda
From Oracle Database 12c Release 2you could use TO_NUMBERwith DEFAULT ... ON CONVERSION ERROR:
从Oracle Database 12c Release 2您可以使用TO_NUMBER与DEFAULT ... ON CONVERSION ERROR：
SELECT TO_NUMBER('*' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;
Or CAST:
或者CAST：
SELECT CAST('*' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;
回答by sOliver
select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, '^(-|+)?\d+(\.|,)?(\d+)?$')), 0) from dual;
It will convert 123to 123, but 123aor 12a3to 0.
它会将123转换为123，但将123a或12a3 转换为0。
回答by stjohnroe
Fitting the original question and rather old skool
适合原始问题和相当老的 skool
select a, decode(trim(translate(b,'0123456789.',' ')),null,to_number(b),0) from
(
select '1' a, 'not a number' b from dual
union
select '2' a, '1234' b from dual
)
回答by usbo
select DECODE(trim(TRANSLATE(replace(replace(A, ' '), ',', '.'), '0123456789.-', ' ')),
null,
DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '.', INSTR(replace(replace(A, ' '), ',', '.'), '.') + 1),
0,
DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '-', 2),
0,
TO_NUMBER(replace(replace(A, ' '), ',', '.'))))) A
from (select '-1.1' A from DUAL union all select '-1-1' A from DUAL union all select ',1' A from DUAL union all select '1..1' A from DUAL) A;
This code excludes such strings as: -1-1, 1..1, 12-2 and so on. And I haven't used regular expressions here.
此代码不包括以下字符串：-1-1、1..1、12-2 等。而且我这里没有使用正则表达式。
回答by Mike Meyers
It's probably a bit messy rolling your own regexp to test for a number, but the code below might work. I think the other solution by Gabe involving a user defined function is more robust since you are using the built in Oracle functionality (and my regexp is probably not 100% correct) but it might be worth a go:
滚动您自己的正则表达式来测试数字可能有点麻烦，但下面的代码可能会起作用。我认为 Gabe 涉及用户定义函数的另一个解决方案更健壮，因为您使用的是内置的 Oracle 功能（我的正则表达式可能不是 100% 正确），但它可能值得一试：
with my_sample_data as (
select '12345' as mynum from dual union all
select '54-3' as mynum from dual union all
select '123.4567' as mynum from dual union all
select '.34567' as mynum from dual union all
select '-0.3462' as mynum from dual union all
select '0.34.62' as mynum from dual union all
select '1243.64' as mynum from dual
)
select
mynum,
case when regexp_like(mynum, '^-?\d+(\.\d+)?$')
then to_number(mynum) end as is_num
from my_sample_data
This will then give the following output:
这将给出以下输出：
MYNUM IS_NUM
-------- ----------
12345 12345
54-3
123.4567 123.4567
.34567
-0.3462 -0.3462
0.34.62
1243.64 1243.64
回答by Victor H
A combination of previous solutions (from @sOliver and @Mike Meyers) and trying to grab as much numbers as possible by removing the last '$' from REGEXP.
以前的解决方案（来自@sOliver 和@Mike Meyers）的组合，并试图通过从 REGEXP 中删除最后一个 '$' 来获取尽可能多的数字。
It can be used to filter the actual number from a configuration table, and have a "kind-of" comment next to the number as '12 Days'.
它可用于从配置表中过滤实际数字，并在数字旁边有一个“种类”注释，如“12 天”。
with my_sample_data as (
select '12345' as mynum from dual union all
select '123.4567' as mynum from dual union all
select '-0.3462' as mynum from dual union all
select '.34567' as mynum from dual union all
select '-.1234' as mynum from dual union all
select '**' as mynum from dual union all
select '0.34.62' as mynum from dual union all
select '24Days' as mynum from dual union all
select '42ab' as mynum from dual union all
select '54-3' as mynum from dual
)
SELECT mynum,
COALESCE( TO_NUMBER( REGEXP_SUBSTR( mynum, '^(-|+)?\d*(.|,)?(\d+)?') ) , 0) is_num
FROM my_sample_data;
with my_sample_data as (
select '12345' as mynum from dual union all
select '123.4567' as mynum from dual union all
select '-0.3462' as mynum from dual union all
select '.34567' as mynum from dual union all
select '-.1234' as mynum from dual union all
select '**' as mynum from dual union all
select '0.34.62' as mynum from dual union all
select '24Days' as mynum from dual union all
select '42ab' as mynum from dual union all
select '54-3' as mynum from dual
)
SELECT mynum,
COALESCE( TO_NUMBER( REGEXP_SUBSTR( mynum, '^(-|+)?\d*(.|,)?(\d+)?') ) , 0) is_num
FROM my_sample_data;
would give
会给
MYNUM IS_NUM
-------- ----------
12345 12345
123.4567 123.4567
-0.3462 -0.3462
.34567 0.34567
-.1234 -0.1234
** 0
0.34.62 0.34
24Days 24
42ab 42
54-3 54
回答by Gultekin
Best method seems to be the function solution but if you don't have necessary privileges in the environment you are struggling (like me), then you can try this one:
最好的方法似乎是函数解决方案，但是如果您在挣扎的环境中没有必要的特权（像我一样），那么您可以尝试以下方法：
SELECT
CASE
WHEN
INSTR(TRANSLATE('123O0',
' qwertyu?op?üasdfghjkl?izxcvbnm??QWERTYUIOP?üASDFGHJKL??ZXCVBNM??~*\/(){}&%^#$<>;@|:_=',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
),
'X') > 0
THEN 'Y'
ELSE 'N'
END is_nonnumeric
FROM DUAL
By the way: In my case the problem was due to "," and "." :) So take that into consider. Inspired from this one. Also this oneseems more concise.
顺便说一句：在我的情况下，问题是由于“，”和“。” :) 所以考虑到这一点。从启发这一个。而且这个看起来更简洁。
By the way 2: Dear Oracle, can you please create some built-in functions for such small but invaluable needs?
顺便说一句2：亲爱的Oracle，能否请您为这种小而无价的需求创建一些内置函数？
