Is there a function in Oracle (9i or 10g) that can be used to check whether the value of a varchar2 variable has numeric-only, alpha-only, or alpha-numeric characters?
Answer:
Daniel, You can use TO_NUMBER on a string to determine, for many cases at least, if it is a valid number. Other than that, you will need to write your own function to determine if a string consists solely of characters or is a blend of the two.
Of course, it's even better when you don't have to write such a function yourself, so I am going to post this challenge as the next puzzle for visitors to this column!
Using TO_NUMBER to handle all possibilities, particulary when you are trying to take into account differences between character sets, is more challenging. For example, the string "1.555,77" is not a valid number when you assume typical U.S. numeric delimiters; it is, however, a valid number in Sweden.
There are additional complications, as is clear from this quote from the SQL Reference documentation: "The suffixes f (F) and d (D) are supported only in floating-point number literals, not in character strings that are to be converted to NUMBER. That is, if Oracle is expecting a NUMBER and it encounters the string '9', then it converts the string to the number 9. However, if Oracle encounters the string '9f', then conversion fails and an error is returned."
Thus, if you would like "123.445d" to be considered a valid number, additional programming besides a call to TO_NUMBER would also be required.
No comments:
Post a Comment