带数字的字符串比较

求助 带数字的字符串比较
数据库里面有：
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 code
SELECT 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 code
with 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;