提问人：zerkms 提问时间：12/20/2010 最后编辑：Lukasz Szozdazerkms 更新时间：10/23/2018 访问量：83588
“安全” TO_NUMBER（）
"Safe" TO_NUMBER()
问：
SELECT TO_NUMBER('*') FROM DUAL
这显然给了我一个例外：
ORA-01722：编号无效
有没有办法“跳过”它并得到或代替？0NULL
整个问题：我有字段，其中包含数字而不是几乎 ;-）（比如），我需要从列中选择最大的数字。NVARCHAR2*
是的，我知道这是一个糟糕的设计，但这就是我现在需要的...... ：-S
更新：
就我自己而言，我已经解决了这个问题
COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)
答：
16赞
Gabe
12/20/2010
#1
我找不到比这更好的东西了：
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;
评论
0赞
zerkms
12/20/2010
是的，我也决定编写我自己的函数，与您的函数有点不同（因为应该成为，我没有提到，对不起）。54-354
11赞
milan
12/20/2010
你最好在这里只忽略 ORA-01722。
5赞
Vadzim
2/6/2015
请注意，自定义 pl/sql 函数具有显著的性能开销，可能不适合繁重的查询。
2赞
Andrew Spencer
4/5/2019
请注意下面的答案，显示了如何从 12c r2 开始本地处理此问题 stackoverflow.com/a/45886745/587365
1赞
stackunderflow
11/11/2020
我同意米兰关于只忽略 ORA-01722 并会写exception when invalid_number then return 0;
1赞
Mike Meyers
12/21/2010
#2
滚动自己的正则表达式来测试数字可能有点混乱，但下面的代码可能有效。我认为 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
然后，这将给出以下输出：
MYNUM IS_NUM
-------- ----------
12345 12345
54-3
123.4567 123.4567
.34567
-0.3462 -0.3462
0.34.62
1243.64 1243.64
7赞
stjohnroe
12/21/2010
#3
适合原来的问题和相当古老的 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
)
评论
0赞
stjohnroe
12/21/2010
@Ronnis，内置功能通常是，但正如您所说，它们表现良好。在 Oracle7 周围工作（从内存）
0赞
zerkms
12/21/2010
这将从字符串 .我没有在问题中提到它，但在这种情况下，我希望（这就是我使用正则表达式的解决方案所做的）。54354-354
0赞
stjohnroe
12/21/2010
@zerkins，实际上以为它会从 54-3 产生 0，无论如何，正如我所说，在评论之前回答了原始问题。不过，就像您的最终解决方案一样。
0赞
Ilya Kharlamov
11/28/2014
如果数字包含空格，则失败： select a， decode（trim（translate（b，'0123456789.'，' '）），null，to_number（b），0） from （ select '1' a， '12 34' b from dual ）
0赞
EvilEddie
10/17/2017
无法在我的应用程序中使用正则表达式，所以这效果很好
32赞
pweitzman
4/27/2013
#4
COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+(\.\d+)?')), 0)
还将获得小数位数为 0 >（小数点右侧的数字）的数字。
评论
3赞
hmqcnoesy
8/26/2016
这是一个很好的解决方案，但应针对每个特定场景调整正则表达式。这个正则表达式可能效果更好：它允许负数和以 开头的数字，不允许非数字尾随字符。正则表达式生成 if contains 和 field' contains ， if contains 等。^\-?\d*\.?\d*$.^\d+(\.\d+)?1field1x0' if 0.0.10field0 6
12赞
sOliver
6/25/2014
#5
select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, '^(-|+)?\d+(\.|,)?(\d+)?$')), 0) from dual;
它会将 123 转换为 123，但将 123a 或 12a3 转换为 0。
1赞
usbo
9/3/2014
#6
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;
此代码不包括以下字符串：-1-1、1..1、12-2 等。而且我在这里没有使用正则表达式。
58赞
Lukasz Szozda
8/26/2017
#7
从您可以将TO_NUMBER与：Oracle Database 12c Release 2DEFAULT ... ON CONVERSION ERROR
SELECT TO_NUMBER('*' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;
或：CAST
SELECT CAST('*' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;
评论
2赞
Paschi
4/12/2021
优秀的解决方案。是的，确认：版本 12.1.0.2.0 - 不支持这个 yeet
0赞
Victor H
2/21/2018
#8
结合以前的解决方案（来自 @sOliver 和 @Mike Meyers），并试图通过从 REGEXP 中删除最后一个“$”来获取尽可能多的数字。
它可用于从配置表中筛选实际数字，并在数字旁边有一个“kind-of”注释为“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;
会给
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
0赞
Gultekin
3/10/2018
#9
最好的方法似乎是功能解决方案，但如果你在你正在挣扎的环境中没有必要的权限（像我一样），那么你可以试试这个：
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
顺便说一句：就我而言，问题是由于“，”和“.”:)所以要考虑到这一点。灵感来自这个。此外，这个似乎更简洁。
顺便说一句 2：亲爱的 Oracle，您能为如此小但宝贵的需求创建一些内置函数吗？
评论
REGEXP_SUBSTR(field, '^\d+')