Página 1 de 1
Como identificar se um valor é numérico no "Select"
Enviado: Qui, 20 Dez 2007 10:08 am
por madriano
Olá pessoal.
Minha dúvida é teoricamente básica. Tenho uma consulta na seguinte estrutura:
Selecionar tudo
SELECT codigo, nome,
NVL(ROUND(AVG(TO_NUMBER(NOTA_FINAL)),2),'-1') AS MEDIA_ALUNO
FROM nota
WHERE ...
Muito bem, o problema é especificamente em cima do campo "Nota_final". O valor onde se é armazenado é um campo em aberto, ou seja, posso colocar tanto valores númerico quanto textuais.
Preciso de uma função para poder identificar o tipo de dados retornado, a fim de utilizar junto com uma função CASE.
[]´s
Enviado: Qui, 20 Dez 2007 10:43 am
por Trevisolli
Brother,
Tenta utilizar esta idéia abaixo, acho q dá pra pegar:
Selecionar tudo
DECLARE
V_VALOR VARCHAR2(100);
BEGIN
SELECT TO_NUMBER('A')
INTO V_VALOR
FROM DUAL;
EXCEPTION
-- Se cair aqui, é porque é ALFANUMÉRICO, ou seja,
-- não deixou realizar o TO_NUMBER
WHEN OTHERS THEN
V_VALOR := 0;
END;
Enviado: Qui, 20 Dez 2007 12:17 pm
por dr_gori
Se você quiser testar isso em várias linhas do select, creio que o ideal seja criar uma função que teste isso. Daí você pode usar tranquilamente em várias linhas sem problema e sem dar ERRO.
Por aí...
Enviado: Sex, 21 Dez 2007 7:49 am
por madriano
... mas ainda gostaria de poder encaixar essa verificação direto na sql da view.
Com function
Enviado: Sex, 21 Dez 2007 8:17 am
por madriano
Tentei criar uma function, mas como sou leigo nessa parte acho que fiz alguma bobagem:
Selecionar tudo
CREATE OR REPLACE FUNCTION "LYCEUM"."RETORNA_NUMERICO" (
VALOR_ENVIADO character varying
)
RETURN NUMBER
IS VALOR_RETORNADO NUMBER(3,2);
begin
SELECT TO_NUMBER(VALOR_ENVIADO)
INTO VALOR_RETORNADO
FROM DUAL;
EXCEPTION
-- Se cair aqui, é porque é ALFANUMÉRICO, ou seja,
-- não deixou realizar o TO_NUMBER
WHEN OTHERS THEN
VALOR_RETORNADO := -1;
RETURN(VALOR_RETORNADO);
end;
Mas ao fazer a chamada:
Selecionar tudo
SELECT RETORNA_NUMERICO('4.5') FROM DUAL
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "LYCEUM.RETORNA_NUMERICO", line 15
Enviado: Sex, 21 Dez 2007 9:06 am
por Tineks
Opa e ai tudo beleza?
eu montei uma package com uma procedure e uma funcao.. a procedure consulta os dados e a função valida se é numero ou não. segue o código dela.
Selecionar tudo
CREATE OR REPLACE PACKAGE PCK_CRISTIANO IS
FUNCTION fun_E_NUMERO (p_VALOR VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
PROCEDURE prc_CONSULTA;
END;
/
CREATE OR REPLACE PACKAGE BODY PCK_CRISTIANO IS
FUNCTION fun_E_NUMERO (p_VALOR VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
v_VALOR NUMBER;
BEGIN
v_VALOR := TO_NUMBER(p_VALOR);
RETURN ' é número';
EXCEPTION
WHEN OTHERS THEN
RETURN ' não é número';
END;
PROCEDURE prc_CONSULTA IS
BEGIN
FOR reg IN (SELECT NOTA_FINAL,
fun_E_NUMERO (NOTA_FINAL) E_NUMERO
FROM MENS_ERRO) LOOP
DBMS_OUTPUT.put_line(' O valor '||reg.NOTA_FINAL||' '||reg.E_NUMERO);
END LOOP;
END;
END;
aqui segue o resultado da execução dela.
Selecionar tudo
SQL> select nome, nota_final from mens_erro;
NOME NOTA_FINAL
---------------------------------------- ----------
Alberto 9.00
Maria 8.00
Zuleica X
Claudia 7.50
SQL> BEGIN
2 PCK_CRISTIANO.prc_consulta;
3 END;
4 /
O valor 9.00 é número
O valor 8.00 é número
O valor X não é número
O valor 7.50 é número
PL/SQL procedure successfully completed.
se tiver alguma dúvida é só falar.
[]'s
Re: Como identificar se um valor é numérico no "Select"
Enviado: Ter, 14 Nov 2017 4:02 pm
por madriano
Olá pessoal.
Eis que após alguns anos, ao fazer uma manutenção na base Oracle, me deparo novamente com a situação deste tópico. Assim sendo, por uma questão de honra, decidi resolver o problema definitivamente
. Não testei a solução com package porque não se enquadraria para mim no formato de consulta que estou tentando executar. Enfim, a solução é mais simples do que pensava... corrigindo apenas a solução com "function":
Selecionar tudo
CREATE OR REPLACE FUNCTION "LYCEUM"."RETORNA_NUMERICO"
(VALOR_ENVIADO IN VARCHAR2)
RETURN NUMBER
IS VALOR_RETORNADO NUMBER;
begin
SELECT TO_NUMBER(VALOR_ENVIADO) INTO VALOR_RETORNADO FROM DUAL;
-- A linha dá para simplificar ainda mais, colocando simplesmente
-- VALOR_RETORNADO := TO_NUMBER(VALOR_ENVIADO);
RETURN(VALOR_RETORNADO);
EXCEPTION
WHEN OTHERS THEN
RETURN(-1);
end;
Observem que declarei a variável "Valor_Retornado" somente como number, sem escala.
A versão do Oracle é a 9.2.
[]´s
Re: Como identificar se um valor é numérico no "Select"
Enviado: Qui, 16 Nov 2017 1:54 pm
por DanielNN
Olá,
se a versão do banco fosse a partir da 10, poderia utilizar expressões regulares.
Eis uma possível solução usando regex:
Via PLSQL:
Selecionar tudo
DECLARE
N_NUMBER VARCHAR2(20) := '12345A';
BEGIN
IF (regexp_like(N_NUMBER, '^[[:digit:]]+$'))THEN
DBMS_OUTPUT.put_line('NUMBER');
ELSE
DBMS_OUTPUT.put_line('NOT');
END IF;
END;
Diretamente via SQL:
Selecionar tudo
SELECT *
FROM TABLE T
WHERE REGEXP_LIKE (T.CAMPO,'^[[:digit:]]+$');
Re: Como identificar se um valor é numérico no "Select"
Enviado: Qui, 16 Nov 2017 2:15 pm
por DanielNN
Para versão 9, é possível também usar a função TRANSLATE para excluir letras:
Selecionar tudo
WITH testdata AS
(SELECT 'abcdef' txt FROM DUAL UNION ALL
SELECT '1234567' txt FROM DUAL union all
SELECT '0' txt FROM DUAL union all
SELECT '123a4567x00' txt FROM DUAL union all
SELECT '123.4567,00' txt FROM DUAL
)
select txt,
translate(txt,'a1234567890','a') sem_numeros,
translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ''á"çéíóúàèìòùâêîôûãõëü¿§¦¹²³£¢¬ªº°¨½¼¾Æ','0') so_numeros
from testdata
where txt = translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ''á"çéíóúàèìòùâêîôûãõëü¿§¦¹²³£¢¬ªº°¨½¼¾Æ','0') /*so_numeros*/
O problema é colocar todos caracteres que você não quer considerar na lista.