Use max in a VARCHAR to get result > 999999?

Question

What if somebody made a column as VARCHAR2(256 CHAR) and there are only numbers in this column. I would like to get the highest number. The problem is: the number is something > 999999 but a Max to a varchar is always giving me a max number of 999999

I tried to_number(max(numbers), '9999999999999') but i still get 999999 back, at that cant be. Any ideas? Thank you

Answer 1

the best way is to

First Solution convert the column in numeric

or

Second Solution convert data in you query in numeric and than get data...

Example

 select max(col1) from(
  select to_number(numbers) as col1 from table ) d

It has to be this way because if you call MAX() before TO_NUMBER(), it will sort alphabetically, and then 999999 is bigger than 100000000000. Note that applying TO_NUMBER() to a varchar2 column incurs the risk of an INVALID_NUMBER exception, should the column containing any non-numeric characters. This is why the first proposed solution is to be preferred.

Answer 2

In Oracle, the NUMBER type contains base 100 floating point values which have a precision of 38 significant digits, and a max value of 9999...(38 9's) x 10^125. There are two questions at issue - the first is whether a NUMBER can contain a value converted from a 256 character string, and the second is if two such values which are 'close' in numeric terms can be distinguished.

Let's start with taking a 256 character string and trying to convert it to a number. The obvious thing to do is:

SELECT TO_NUMBER('9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999') AS VAL
  FROM DUAL;

Executing the above we get:

ORA-01426: numeric overflow

which, having paid attention earlier, we expected. The largest exponent that a NUMBER can handle is 125 - and here we're trying to convert a value with 256 significant digits. NUMBER's can't handle this. If we cut the number of digits down to 125, as follows:

SELECT TO_NUMBER('99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999') AS VAL
  FROM DUAL;

It works fine, and our answer it 1E125.

<blink>

WHOA! WAIT!! WHAT??? The answer is 1 x 10^125??? What about all those 9's?!?!?!?

Remember earlier I'd mentioned that an Oracle NUMBER is a floating point value with a maximum precision of 38 and a maximum exponent of 125. From the point of view of TO_NUMBER 125 9's all strung together can't be exactly represented - too many digits (remember, max. precision of 38 (more on this later)). So it does the absolute best it can - it converts the first 38 digits (all of which are 9's) and then says "How should I best round this off to make the result A) representative of the input and B) as close as I can get to what I was given?". In this case it looks at digit 39, sees that it's a 9, and decides to round upward. As all the other digits are also 9's, it continues rounding neatly until it ends up with 1 as the remaining mantissa digit.

* Later, back at the ranch... *

OK, earlier I'd mentioned that NUMBER has a precision of 38 digits. That's not entirely true - it can actually differentiate between values with up to 40 digits of precision, at least sometimes, if the wind is right, and you're going downhill. Here's an example:

SELECT CASE
         WHEN to_number('9999999999999999999999999999999999999999') >
              to_number('9999999999999999999999999999999999999998')
           THEN 'Greater'
           ELSE 'Not greater'
       END AS VAL
  FROM DUAL;

Those two values each have 40 digits (counting is left as an exercise to the extremely bored reader :-). If you execute the above you'll get back 'Greater', showing that the comparison of two 40 digit values succeeded.

Now for some fun. If you add an additional '9' to each string, making for a 41 digit value, and re-execute the statement it'll return 'Not greater'.

<blink>

WAIT! WHAT?? WHOA!!! Those values are obviously different! Even a TotalFool (tm) can see that!!

The problem here is that a 41 digit number exceeds the precision of the NUMBER type, and thus when TO_NUMBER finds it has a value this long it starts discarding digits on the right side. Thus, even though those two really big numbers are clearly different to you and me, they're not different at all once they've been folded, spindled, mutilated, and converted.

So, what are the takeaways here?

1 - To the OP's original question - you'll have to come up with another way to compare your number strings besides using NUMBER because Oracle's NUMBER type can't hold 256 digit values. I suggest that you normalize the strings by making sure ALL the values are 256 digits long, adding zeroes on the left as needed, and then a string comparison should work OK.

2 - Floating point numbers prove the existence of (your favorite deity/deities here) by negation, as they are clearly the work of (your favorite personification of evil here). Whenever you work with them (as we all have to, sooner or later) you should remember that they are the foul byproducts of malignant evil, waiting to lash out at you when you least expect it.

3 - There is NO point three! (And extra credit for those who can identify without resorting to an extra-cranial search engine where this comes from :-)

Share and enjoy.

Answer 3

If you mean that the numbers in the column can be that big (256 digits), you could try something like this:

SELECT numbers
FROM (
  SELECT numbers
  FROM table_name
  ORDER BY LPAD(numbers, 256) DESC
)
WHERE rownum = 1

or like this:

SELECT LTRIM(MAX(LPAD(numbers, 256))) AS numbers
FROM table_name