oracle - to_integer - "Seguro" TO_NUMBER()

to_number sql server

Question

SELECT TO_NUMBER(''*'') FROM DUAL

Obviamente, esto me da una excepción:

ORA-01722: número inválido

¿Hay alguna forma de "omitirlo" y obtener 0 o NULL lugar?

Todo el problema: tengo el campo NVARCHAR2 , que contiene números y no casi ;-) (como * ) y necesito seleccionar el número más grande de la columna.

Sí, sé que es un diseño terrible, pero esto es lo que necesito ahora ...: -S

UPD :

Para mí, he resuelto este problema con

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, ''^/d+'')), 0)

Answer 1

Ajustando la pregunta original y bastante viejo 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
)

Answer 2

Desde Oracle Database 12c Release 2 , puede usar TO_NUMBER con DEFAULT ... ON CONVERSION ERROR :

SELECT TO_NUMBER(''*'' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

O CAST :

SELECT CAST(''*'' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

Answer 3

El mejor método parece ser la solución de función, pero si no tiene los privilegios necesarios en el entorno en el que está luchando (como yo), entonces puede probar este:

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

Por cierto: en mi caso, el problema se debió a "," y "." :) Así que tenlo en cuenta. Inspirado de este . También este parece más conciso.

Por cierto, 2: Querido Oracle, ¿podrías crear algunas funciones integradas para necesidades tan pequeñas pero invaluables?

Answer 4

No pude encontrar nada mejor que esto:

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;

Answer 5

Probablemente sea un poco complicado rodar tu propia expresión regular para probar un número, pero el siguiente código podría funcionar. Creo que la otra solución de Gabe que implica una función definida por el usuario es más robusta ya que está utilizando la funcionalidad incorporada de Oracle (y mi expresión regular probablemente no es 100% correcta) pero podría valer la pena ir:

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

Esto dará el siguiente resultado:

MYNUM IS_NUM
-------- ----------
12345 12345
54-3
123.4567 123.4567
.34567
-0.3462 -0.3462
0.34.62
1243.64 1243.64

Answer 6

Una combinación de soluciones anteriores (de @sOliver y @Mike Meyers) e intentando obtener la mayor cantidad de números eliminando el último ''$'' de REGEXP.

Se puede usar para filtrar el número real de una tabla de configuración y tener un comentario tipo "al lado" junto al número como ''12 días ''.

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;

daría

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

Answer 7

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, ''^/d+(/./d+)?'')), 0)

también obtendrá números con escala> 0 (dígitos a la derecha del punto decimal).

Answer 8

select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, ''^(-|+)?/d+(/.|,)?(/d+)?$'')), 0) from dual;

Convierta 123 a 123 , pero 123a o 12a3 a 0 .

Answer 9

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;

Este código excluye cadenas como: -1-1, 1..1, 12-2 y así sucesivamente. Y no he usado expresiones regulares aquí.