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í.