Oracle SQL MAX() and MIN() with string

Question

I'm having an issue retrieving the MAX number from database. The catch is that the numbers are in string format since they contain an underscore.

Example case: I have 3 numbers in the database:

123456_1, 123456_2, 123456_13,

The MAX number I would like to fetch is 123456_13 (basically the biggest after underscore), but since they are string, Oracle processes them alphabetically and orders ascending as:

1. 123456_1
2. 123456_13
3. 123456_2

making 123456_2 the MAX.

This would be possible, if I would not need to display the actual MAX or MIN number by replacing the underscore and converting to number:

SELECT
  MAX(TO_NUMBER(REPLACE(number,'_',''))) max_nr,
  MIN(TO_NUMBER(REPLACE(number,'_',''))) min_nr
FROM
...

This would return 12345613 max_nr 1234561 min_nr from my 3 examples, but I need to fetch the actual numbers with underscore and display that.

Is there any way to do this?

Answer 1

If you want to get the min and max at the same time, you could use the keep syntax twice, ordering by the numeric equivalent of all characters after the underscore:

select
  max(your_number)
    keep (dense_rank last
      order by to_number(substr(your_number, instr(your_number, '_') + 1))
    ) as max_number,
  min(your_number)
    keep (dense_rank first
      order by to_number(substr(your_number, instr(your_number, '_') + 1))
    ) as min_number
from your_table

MAX_NUMBER	MIN_NUMBER
123456_13	123456_1

fiddle

The instr(your_number, '_') gives the position of the underscore; substr(your_number, instr(your_number, '_') + 1) gives you everything after that underscore.

That would work if the values weren't always the same number of digits before the underscore, which could be a problem if you just remove it.