Como identificar se um valor é numérico no "Select"

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
madriano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Qui, 20 Dez 2007 9:58 am
Localização: PR

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
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

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; 
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5023
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

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.

:-o
madriano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Qui, 20 Dez 2007 9:58 am
Localização: PR

... mas ainda gostaria de poder encaixar essa verificação direto na sql da view.
madriano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Qui, 20 Dez 2007 9:58 am
Localização: PR

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
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 365
Registrado em: Ter, 24 Mai 2005 2:24 pm
Localização: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

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
madriano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Qui, 20 Dez 2007 9:58 am
Localização: PR

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 :-o . 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
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

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:]]+$');
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

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.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 5 visitantes