The other day I had reason to be investigating the Number and Number (Unformatted) data types. There are some interesting features that might be useful to others. So, here are some snippets that you might not have seen before concerning Number formats and output.
Let’s begin with the basic challenge. We have a text attribute that is sent by a system to Oracle Intelligent Advisor. That piece of text is in fact a number.
We need to convert this to a number format for processing by Oracle Intelligent Advisor. So we can use two functions to help up. We can use IsNumber() to test the text value and then Number() to actually convert. What you get will depend both on what you feed in to it, and how you handle the output.
Take for example the Number() function. I’m guessing you would probably understand that feeding it “200” as text would get you 200 as a number. But what about this one, maybe you didn’t know that the Number() function has hidden powers (pun intended):
Yes, the Number() function will understand scientific notation. In fact it will also accept numbers using F for float or D for double in the string value – which comes in handy when an external system sends you that kind of data (so 2.4F is also acceptable – but does not change the underlying number value since Oracle Intelligent Advisor does not (yet?) have Float or Double, it only has Number or Currency. Notice also that the IsNumber() function shown above happily returns true – 2e4 is recognized as a number.
Notice in the screen capture above that the unformatted number and normal number data types display differently – of course – but the underlying numeric value is the same. Another oddity of working with unformatted and normal numbers can be seen in this screen capture – notice how the unformatted uses a truncated output.
There are plenty more things to learn about how Oracle Intelligent Advisor handles data types and conversions, as we shall endeavour to show you in upcoming articles. Find out more about Number() here. Enjoy learning about Number formats.