带数字的字符串比较_Oracle管理

求助带数字的字符串比较
数据库里面有：
oracle123
oracle2
oracle45
oracle9
这些字符串，我想取出最大的那条 oracle123，但是用max选出的是oracle9，求助

------解决方案--------------------

SQL code

select  'oracle'||max(to_number(replace(val, 'oracle', ''))) maxval from t;
------解决方案--------------------
如果都是以“oracle”+数字，用这种方法是没有问题的
探讨
SQL code

select  'oracle'||max(to_number(replace(val, 'oracle', ''))) maxval from t;

------解决方案--------------------
SQL codeSELECT SubStr(id,1,6)||Max(To_Number(SubStr(id,7,Length(id)))) FROM test GROUP BY SubStr(id,1,6);
------解决方案--------------------
探讨
SQL code


select  'oracle'||max(to_number(replace(val, 'oracle', ''))) maxval from t;

------解决方案--------------------
select substr(val,1,6)||max(to_number(substr(val,7,length(val)-6))) from t group by substr(val,1,6);
------解决方案--------------------
探讨

6楼求教用“正则获取字符串中的数字”？

------解决方案--------------------
SQL codewith t as (select 'oracle123' name from dualunion allselect 'oracle2' name from dualunion allselect 'oracle45' name from dualunion allselect 'oracle9' name from dual)select * from (select  t.name from t order by to_number(regexp_substr(t.name,'\d+')) desc) where rownum=1;