verificar si la función “es un número” en Oracle

Estoy tratando de verificar si un valor de una columna en una consulta de Oracle (10g) es un número para poder compararlo. Algo como:

select case when ( is_number(myTable.id) and (myTable.id >0) ) then 'Is a number greater than 0' else 'it is not a number' end as valuetype from table myTable 

¿Alguna idea sobre cómo verificar eso?

Suponiendo que la columna ID en myTable no está declarada como NÚMERO (lo cual parece una elección extraña y probablemente problemática), puede escribir una función que intente convertir el ID (presumiblemente VARCHAR2) en un número, capte la excepción, y devuelve una ‘S’ o una ‘N’. Algo como

 CREATE OR REPLACE FUNCTION is_number( p_str IN VARCHAR2 ) RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE IS l_num NUMBER; BEGIN l_num := to_number( p_str ); RETURN 'Y'; EXCEPTION WHEN value_error THEN RETURN 'N'; END is_number; 

A continuación, puede insertar esa llamada en una consulta, es decir,

 SELECT (CASE WHEN is_number( myTable.id ) = 'Y' AND myTable.id > 0 THEN 'Number > 0' ELSE 'Something else' END) some_alias FROM myTable 

Tenga en cuenta que aunque PL / SQL tiene un tipo de datos booleanos, SQL no. Entonces, si bien puede declarar una función que devuelve un valor booleano, no puede usar dicha función en una consulta SQL.

Una idea adicional, mencionada aquí es usar una expresión regular para verificar:

 SELECT foo FROM bar WHERE REGEXP_LIKE (foo,'^[[:digit:]]+$'); 

Lo bueno es que no necesita una función PL / SQL por separado. La parte potencialmente problemática es que una expresión regular puede no ser el método más eficiente para una gran cantidad de filas.

La respuesta de REGEXP_LIKE usando REGEXP_LIKE es la idea correcta, pero no admite números flotantes. Este lo hará …

Valores devueltos que son numéricos

 SELECT foo FROM bar WHERE REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$'); 

Valores devueltos no numéricos

 SELECT foo FROM bar WHERE NOT REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$'); 

Puede probar sus expresiones regulares hasta que su corazón esté satisfecho en http://regexpal.com/ (pero asegúrese de seleccionar la checkbox en los saltos de línea para este).

Puede usar la función de expresión regular ‘regexp_like’ en ORACLE (10g) como se muestra a continuación:

 select case when regexp_like(myTable.id, '[[:digit:]]') then case when myTable.id > 0 then 'Is a number greater than 0' else 'Is a number less than or equal to 0' end else 'it is not a number' end as valuetype from table myTable 

Este es un duplicado potencial de Buscar filas que no contienen datos numéricos en Oracle . También vea: ¿Cómo puedo determinar si una cadena es numérica en SQL? .

Aquí hay una solución basada en la de Michael Durrant que funciona para enteros.

 SELECT foo FROM bar WHERE DECODE(TRIM(TRANSLATE(your_number,'0123456789',' ')), NULL, 'number','contains char') = 'number' 

Adrian Carneiro publicó una solución que funciona para decimales y otros. Sin embargo, como señaló Justin Cave, esto clasificará incorrectamente cadenas como ‘123.45.23.234’ o ‘131 + 234’.

 SELECT foo FROM bar WHERE DECODE(TRIM(TRANSLATE(your_number,'+-.0123456789',' ')), NULL, 'number','contains char') = 'number' 

Si necesita una solución sin PL / SQL o REGEXP_LIKE, esto puede ayudar.

Estoy en contra de usar when others así lo usaría (devolviendo un “entero booleano” debido a SQL no soportando booleanos)

 create or replace function is_number(param in varchar2) return integer is ret number; begin ret := to_number(param); return 1; --true exception when invalid_number then return 0; end; 

En la llamada SQL usarías algo como

 select case when ( is_number(myTable.id)=1 and (myTable.id >'0') ) then 'Is a number greater than 0' else 'it is not a number or is not greater than 0' end as valuetype from table myTable 

¿Cómo se define la columna? Si es un campo varchar, entonces no es un número (o almacenado como uno). Oracle puede hacer la conversión por usted (por ejemplo, seleccione * de alguna Tabla donde charField = 0), pero solo devolverá las filas donde la conversión sea verdadera y posible. Esto también está lejos de la situación ideal en cuanto a rendimiento.

Entonces, si desea hacer comparaciones de números y tratar esta columna como un número, ¿quizás debería definirse como un número?

Dicho esto, esto es lo que podrías hacer:

 create or replace function myToNumber(i_val in varchar2) return number is v_num number; begin begin select to_number(i_val) into v_num from dual; exception when invalid_number then return null; end; return v_num; end; 

También puede incluir los otros parámetros que tiene el número to_ regular. Use como tal:

 select * from someTable where myToNumber(someCharField) > 0; 

No devolverá ninguna fila que Oracle vea como un número inválido.

Aclamaciones.

 CREATE OR REPLACE FUNCTION is_number(N IN VARCHAR2) RETURN NUMBER IS BEGIN RETURN CASE regexp_like(N,'^[\+\-]?[0-9]*\.?[0-9]+$') WHEN TRUE THEN 1 ELSE 0 END; END is_number; 

Tenga en cuenta que no considerará 45e4 como un número, pero siempre puede cambiar la expresión regular para lograr lo contrario.

@JustinCave – El reemplazo “when value_error” para “when others” es un refinamiento agradable de su enfoque anterior. Este ligero ajuste adicional, aunque es conceptualmente el mismo, elimina el requisito para la definición y la consiguiente asignación de memoria a su variable l_num:

 function validNumber(vSomeValue IN varchar2) return varchar2 DETERMINISTIC PARALLEL_ENABLE is begin return case when abs(vSomeValue) >= 0 then 'T' end; exception when value_error then return 'F'; end; 

Solo una nota para cualquiera que prefiera emular la lógica de formato de números de Oracle usando el enfoque REGEXP “más arriesgado”, por favor no olvide considerar NLS_NUMERIC_CHARACTERS y NLS_TERRITORY.

Esta es mi consulta para encontrar todos los que NO son números:

 Select myVarcharField From myTable where not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\.\d+)?$', '') and not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\,\d+)?$', ''); 

En mi campo, yo sí. y, por desgracia, los números decimales deben tenerlo en cuenta, de lo contrario, solo necesita uno de la restricción.

bien, podrías crear la función is_number para llamar para que tu código funcione.

 create or replace function is_number(param varchar2) return boolean as ret number; begin ret := to_number(param); return true; exception when others then return false; end; 

EDITAR: Por favor, diferir a la respuesta de Justin. Olvidé ese pequeño detalle para una llamada SQL pura …

Función para número de móvil de 10 dígitos de longitud y a partir de 9,8,7 usando regexp

 create or replace FUNCTION VALIDATE_MOBILE_NUMBER ( "MOBILE_NUMBER" IN varchar2 ) RETURN varchar2 IS v_result varchar2(10); BEGIN CASE WHEN length(MOBILE_NUMBER) = 10 AND MOBILE_NUMBER IS NOT NULL AND REGEXP_LIKE(MOBILE_NUMBER, '^[0-9]+$') AND MOBILE_NUMBER Like '9%' OR MOBILE_NUMBER Like '8%' OR MOBILE_NUMBER Like '7%' then v_result := 'valid'; RETURN v_result; else v_result := 'invalid'; RETURN v_result; end case; END; 

Creo que la siguiente solución (basada en el enfoque regexp_like anterior) es óptima:

 function isInteger(vYourValue IN varchar2) return varchar2 is begin return case REGEXP_INSTR(vYourValue,'^[[:digit:]]+$') when 0 then 'F' else 'T' end; end; 

¿Por qué digo esto?

  1. El subconjunto numérico probado se puede modificar como se desee cambiando la expresión regular de forma adecuada.

  2. Se puede usar en SQL, es decir, seleccionar isInteger (myCol) de mytable; ya que devuelve ‘T’ de ‘F’ en lugar de booleano.

  3. Se puede usar de forma nativa en pl / sql, es decir. if isInteger (vMyValue) = ‘T’, entonces ….

  4. Satisface una afirmación de pureza WNDS, WNPS.

  5. No se basa, en mi opinión, en un enfoque de amplio espectro “cuando otros” se acercan para la determinación del resultado.

Tenga en cuenta que los enfoques de expresiones regulares o funciones son varias veces más lentos que las condiciones simples de sql .

Por lo tanto, algunas soluciones heurísticas con aplicabilidad limitada permiten detectar escaneos de gran tamaño.

Existe una solución para casos en los que se sabe con certeza que los valores no numéricos contendrán algunas letras alfabéticas:

 select case when upper(dummy)=lower(dummy) then '~numeric' else '~alpabetic' end from dual 

Y si sabe alguna carta, estará siempre presente en casos no numéricos:

 select case when instr(dummy, 'X')>0 then '~alpabetic' else '~numeric' end from dual 

Cuando los casos numéricos siempre contendrían cero:

 select case when instr(dummy, '0')=0 then '~alpabetic' else '~numeric' end from dual 

si la condición es nula, entonces es el número

 IF(rtrim(P_COD_LEGACY, '0123456789') IS NULL) THEN return 1; ELSE return 0; END IF;