Unanswered: PL/SQL - How can I verify presence of alphanumeric chars in a text field
I've a text column in my table. This col contains mostly numbers, but in some cases can contain alphanumeric value also. In my PL/SQL code, amongst other things, I would like to check if this particular col has such alphanumeric value in case of which I'd like to take certain action, else continue normal flow of program.
Thanks, that's quite useful. Unfortunately it seems it works 10g onwards. I'm having 9i
Any other suggestions that could work in 9i?
Wow, that is an ancient version.
(That'll teach you to include your version number, especially when using a non-supported version )
The only workaround I can see, is to create a function that tries to convert a string to a number and catches all exceptions. If an exception occurred you ignore it and signal that it was not a number, something like:
create or replace function safe_to_number(to_test varchar)
result := to_number(to_test);
exception when others then
result := null;
You will need to adjust the to_number() call to use your expected number format.
But be warned: this is not a really fast solution, especially when you have a lot of exceptions!