Oracle - Validar numeros en cadenas

Hola

El problema que tengo es el siguiente:

En una tabla tengo un campo tipo varchar2, en el cual hay filas que tienen numeros y en otras cadenas. En una consulta necesito convertir a numero todos los registros, desde luego los que son cadena quedarian como 0 dado que no son numeros validos.

Estoy usando la funcion to_number(nombre_del_campo) pero me devuelve el siguiente error:

Error en QUERY
-2147217913: ORA-01722: número no válido

quiciera saber si hay alguna forma de indicar en la consulta que las cadenas que no contengan un numero valido les ponga 0, o bien, alguna otra opcion, se aceptan sugerencias

Muchas gracias

saludos:

Alguna vez hice algo parecido en SQL Server, y tambien se puede hacer en oracle, necesitas hacer validaciones de tipo DECODE o lo que es lo mismo IF anidados, y usando la clausula REGEXP_LIKE, para validar que lo que contenga el campo es un numero, si cualquiera de ellos no lo es, entonces ponlo como cero y si todos coinciden como numericos, entonces puedes convertirlo a numero sin problemas. Es algo complicado y tendras que leer la documentacion de esta clausula, que usa Expresiones Regulares.

Espero te sirva.

Hola:

A mí me pasó algo parecido. Que yo sepa, en Oracle no existe ninguna función que te permita distinguir números de cadenas, sin que te salga el mensaje que dices (Oracle intenta convertir internamente los tipos de datos) . Yo opté por crear mi propia función:

----------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION FNC_NUMERO (p_data VARCHAR2)
RETURN NUMBER IS
v_number NUMBER;
BEGIN
BEGIN
v_number := p_data;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;

if v_number < 0 then
return null;
elsif v_number-floor(v_number)>0 then
return null;
else
return v_number;
end if;

END FNC_NUMERO;
/
----------------------------------------------------------------------------------------------------

A esta función se le pasa una cadena, y retornará el numero (tipo NUMBER) que representa. Si no es un numero entero y positivo, retornará null. Si quieres generalizar para cualquier tipo de número (negativos o decimales), no tienes más que quitar los 2 if-s.

Una vez creada la función, bastaría con llamarla, y en tu caso usar NVL para sustituir el null por 0.

SELECT NVL(FNC_NUMERO(CAMPO),0) FROM TABLA;

Isaac:

Mirá:

Se me ocurre una solución bastante 'simplista':

Create or replace function to_number2(p_cadena in varchar2) return number
is
pl_ret_val number(10);
Begin
Begin
pl_ret_val := to_number(cadena);
Exception
When others then
pl_ret_val := 0;
End;

Return pl_ret_val
End;
/

Con esto lo que hacés es 1- tratás de convertir a numero 2- si te falla, en el when others le asignas 0 a la variable de retorno y 3- retornás la variable de retorno.

Saludos
Ricardo.