The VALUE_ERROR handler didn′t catch the ORA-01858 condition and is why the output shows the SQL error message. The inner exception block can be modified to catch/test for specific error numbers, but accounting for all of the possible types of errors of this nature quickly makes the date approach – at least as far as the account number conversion project is concerned – somewhat cumbersome. Our last approach is elegant and easy to implement. 数据挖掘交友
Approach 4: Using Regular Expressions
A new feature in Oracle (which release?) deals with regular expressions. Long a mainstay of other languages (to include UNIX), regular expression functionality had been missing from Oracle. This approach is so simple and elegant to use that you may wonder why the other three were even mentioned in the first place. The only requirement for using REGEXP is that you must be using, obviously, a version of Oracle that supports it. You know there are companies still using version 7.3.4, and given that REGEXP first appeared several versions later, well, that answers why the other approaches merit consideration. 数据挖掘研究院
So what does REGEXP_LIKE do for us? Let′s go straight to the "only digits" search condition.
数据挖掘工具
SQL> select acctnum from account_test
2 where regexp_like (acctnum,′^[[:digit:]]+ 数据挖掘研究院
Again, a slight modification to the query will filter out the too short 009341 number. The REGEXP_LIKE function performs exactly the same as the first approach using TRANSLATE, that is, results come back one way or the other.
A Slight Extension
Taking either the first or last approach and converting the query/returned result into a function call adds an extremely useful utility users/Oracle have been missing since day one. How do you test for "not a number" or "is a number" in Oracle? By using the first or last approach, you just performed that test. Turn it into a function returning true or false, and grant execute permissions as appropriate, and you can now perform NaN(whatever) tests.
