How to identify if a value is numeric in Select & ";

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 20 Dec 2007 10:08 am

Hello guys.

My question is basic theory. I have a query in the following structure: SELECT code, name, NVL (ROUND (AVG (TO_NUMBER (NOTA_FINAL)) .2), ' -1 ') AS MEDIA_ALUNO FROM note WHERE ...

Well, the problem is specifically on the field \"Nota_final\". The value where it is stored is an open field, i.e. can I put both numeric values as text.

I need a function to be able to identify the type of data returned in order to use along with a CASE function.

[] ´ s
madriano
Location: PR

Poston Thu, 20 Dec 2007 10:43 am

Brother, try to use this idea below, I guess you can get:
Code: Select all

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;
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Thu, 20 Dec 2007 12:17 pm

If you want to test it in multiple lines of select, I believe that the ideal is to create a function to test it. Then you can use quietly on multiple lines without problem and without ERROR.

:-o
dr_gori
Location: Seattle, WA, USA

Thomas F. G

Poston Fri, 21 Dec 2007 7:49 am

... but I still wish I could fit this direct check on sql view.
madriano
Location: PR

Poston Fri, 21 Dec 2007 8:17 am

I tried to create a function, but as I lay there I think I did something stupid:
Code: Select all
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;
But while making the call:
Code: Select all
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
madriano
Location: PR

Poston Fri, 21 Dec 2007 9:06 am

Opa and ai all beleza?

I put together a package with a procedure and a function. the procedure and data query function validates whether is number or not. follow the code.
Code: Select all
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;
here follows the result of executing her.
Code: Select all
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.
If you have any questions just ask.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Tue, 14 Nov 2017 4:02 pm

Hello guys.

Behold, after some years, to do a work on the basis of Oracle, I met again with the situation of this topic. Therefore, as a matter of honor, decided to solve the problem definitely:-. Have not tested the solution with package why don't fit for me in the form of query that I'm trying to run. Anyway, the solution is simpler than I thought ... correcting only solution with " function ":
Code: Select all
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;
Observe that I have declared the variable " Valor_Retornado " only as number, without scale.

Oracle is the version 9.2.

[] ´ s
madriano
Location: PR

Poston Thu, 16 Nov 2017 1:54 pm

Hello, if the version of the Bank were from 10, could use regular expressions.
Here's a possible solution using regex: Via PLSQL:
Code: Select all
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;
Directly via SQL:
Code: Select all
SELECT *
  FROM TABLE T
WHERE REGEXP_LIKE (T.CAMPO,'^[[:digit:]]+$');
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Thu, 16 Nov 2017 2:15 pm

For version 9, you can also use the TRANSLATE function to delete letters:
Code: Select all
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*/
the problem is putting all characters that you do not want to consider the list.
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.



Return to SQL

Who is online

Users browsing this forum: Google [Bot] and 6 guests