Doubt function to_number

[Mônica F]

Help!!!!

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

[Mônica F]

I will explain better ....

I have to compare the Num_nf (Number) field of a table with the Num_doc_origem (ChAR) field of another table. However, as the num_doc_origem column was like char, she accepted the insertion of data that should not, as incredible as it seems had a user who inserted email information instead of putting the note number. That's why I can not turn the field into number.
presents the error: ORA-01722: invalid number

I really need help !!!

Thanks!

Monica F.

[rodfbar]

vixiiiii ... I think you will have to sweep what the note is wrong ... to arrange her first and then do this alter

if you do not want to do this not to give error in your Function You can tmb instead of transforming the char to number transform the number to char ...
Trim (field_char) = trim (to_char (field_number))
]
see if it serves you ...

Abrçs

[Mônica F]

I am going to try!!! thank you!!!!

[Trevisolli]

Mônica,

a tip to fetch these notes at the base, would be test whether it is Number and if not, play in a log table or, concatenate the error.

In this example below, you will fall into others as there is a letter in the field.

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;   
 

Whatever, send it there.

[Mônica F]

Thank you very much! You helped me a lot!

[vetis]

Select all

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 procedure whenever you have any problem this is only call it
___________
Thiago Vetis: -O