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

Accepted Answer

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.

Use max in a VARCHAR to get result > 999999?

Solution

Problem