Printable View
I'm attempting to identify invalid characters stored in a varchar2 column.
Despite it's format, it represents the invoice number in Oracle Receivables.
Unfortunately, I am trying to do a comparison between the 'number' stored and a range of numbers. Because there are a few rows with other ascii characters stored, I'm getting an ora-01722: invalid number returned.
Does anyone have a script which can check for those records with an invalid value?
Hi Warrick
I think the best thing to do is to write a program yourself and maintain it for your future use as well.
Well, if you are interested in checking whether the VARCHAR2 column has any non-numeric characters, then I would write a program to compare the ascii equivalent of each character in the data (of a column for each row) with a the ascii codes of numbers 0 through 9 (ascii equivalent 49 through 57), if there is no match, then obviously the character is non-numeric.
Hope that helps.
Thank you. This is what I had already persued.
Unfortunately, with nearly a million records to parse, this is going to take a significant amount of time and is needed for several columns...