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

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

i attempting highest scheme number set of rows. scheme number preceded sys, select system_id table yield, {sys901,sys87,sys3024.....}

this query i'm attempting use:

select max(replace(system_id,'sys','')) table

the possible results are

{901,87,3024,20,1}

it returning 901 value i'm expecting see 3024 value. assume problem field varchar not number. how address problem, not know.

select max(to_number(replace(system_id,'sys',''))) table;

use to_number convert varchar2 number otherwise oracle compares strings using ascii codes ('9' > '3')

sql oracle