ORA-01722 PL/SQL Error

[tokerago13]

Hi All,
I trying to handle the error ora-01722 in a PL/Sql procedure. I have a columns with rows that have a mixture of varchar2 and number datatype. Originally the column is VARCHAR2. I'm looping through the column and assigning the value to a variable.
What I would like is to check that I only use columns with a numeric value and ignore every other value.
Is it possible to get a one liner, so that I don't have to handle the exception for alphernumeric values.
Thanks in advance.
Tony

 

[carp]

Well, one way to find strings with letters in them would be:

SELECT ...
FROM ...
WHERE NVL(LENGTH(TRIM(TRANSLATE(my_column,'1234567890','          '))),0) > 0;

although I'm sure others will have a much nicer way to do this.