Oracle SQL MAX function not returning max on VARCHAR to NUMBER conversion

Question

I am attempting to get the highest system number from a set of rows. The system number is preceded with SYS, thus select SYSTEM_ID from TABLE would yield, {SYS901,SYS87,SYS3024.....}

This is the query I'm attempting to use:

select MAX(REPLACE(SYSTEM_ID,'SYS','')) from TABLE

The possible results are

{901,87,3024,20,1}

It is returning the 901 value where I'm expecting to see the 3024 value. I assume the problem is that the field is a VARCHAR not a NUMBER. How to address this problem, I do not know.

Answer 1

select MAX(TO_NUMBER(REPLACE(SYSTEM_ID,'SYS',''))) from TABLE;

Use TO_NUMBER to convert VARCHAR2 to NUMBER otherwise Oracle compares strings using their ASCII codes ('9' > '3')

Answer 2

Of course it is returning the max. It is just returning the max according to the rules of string comparisons, not numbers.

To get the numeric max, do a conversion:

select MAX(TO_NUMBER(REPLACE(SYSTEM_ID, 'SYS', ''))) from TABLE