Doubt to_number function

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

Poston Wed, 30 Apr 2008 3:16 pm

Help!!!!

I have a column that is as char, and I need to turn it to number, but have characters in this field how do I remove them.
Because in my select I need to compare this with the field of another table.

:(
Mônica F
Location: São Paulo

Mônica F.

Poston Wed, 30 Apr 2008 4:41 pm

I'll explain better .... I have to compare the num_nf field (number) of a table with the num_doc_origem field (char) in another table. However, as the num_doc_origem column was as char, she accepted the insertion of data that shouldn't, as incredible as it seems had a user who entered the Email learn instead of placing the invoice number. Why I can't turn the field to number.
Displays the error: ORA-01722: invalid Number I Really need help!!!

Thanks!

Monica F.
Mônica F
Location: São Paulo

Mônica F.

Poston Wed, 30 Apr 2008 5:31 pm

vixiiiii ... guess you'll have to sweep which note q this registered wrong ... fix it first and then make the changes that do not want to do this to not give error on your product you can instead of transforming the tmb char to number turn number for char. ..

trim (campo_char) = trim (to_char (campo_number)) to see if it's there ... abrçs
rodfbar
Location: Batatais - SP

Poston Mon, 05 May 2008 8:51 am

I'll try!!! Thank you so much!!!!
Mônica F
Location: São Paulo

Mônica F.

Poston Mon, 05 May 2008 11:33 am

Monica, a hint to get those notes at the base, would test whether she is number, and, if not, throw a log table, or concatenate the error.

In this example below, will fall on OTHERS, because there is a letter in the field.

Code: Select all
DECLARE
  VNUMBER NUMBER;
BEGIN

  SELECT TO_NUMBER(NRO_SEQ)
   INTO VNUMBER
   FROM TESTE
  WHERE ID_PAC = 1
    AND ID_CONS = 2;
 
EXCEPTION 
  WHEN OTHERS THEN
    dbms_output.put_line(' Não é Number.');
END; 

qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Mon, 19 May 2008 12:10 pm

Very very thanks! você ´ s helped me enough!
Mônica F
Location: São Paulo

Mônica F.

Poston Tue, 20 May 2008 2:33 pm

CREATE OR REPLACE FUNCTION Retorna_Numericos (v_string IN CHAR) RETURN CHAR IS BEGIN RETURN REPLACE (TRANSLATE (v_string, TRANSLATE (v_string, ' 0123456789 ', ''), LPAD (' ', LENGTH (v_string), ' ')), ' ', '');
END;

Note: Creating the procedure whenever you have a problem that is just calls it _ _ _ _ _ _ _ _ _ _ _ Thiago Vetis:-
vetis
Location: cariacica - es

Poston Tue, 20 May 2008 2:34 pm

CREATE OR REPLACE FUNCTION Retorna_Numericos (v_string IN CHAR) RETURN CHAR IS BEGIN RETURN REPLACE (TRANSLATE (v_string, TRANSLATE (v_string, ' 0123456789 ', ''), LPAD (' ', LENGTH (v_string), ' ')), ' ', '');
END;

Note: Creating the procedure whenever you have a problem that is just calls it _ _ _ _ _ _ _ _ _ _ _ Thiago Vetis:-
vetis
Location: cariacica - es



Return to SQL

Who is online

Users browsing this forum: No registered users and 1 guest