日期:2014-05-17  浏览次数:20904 次

求助 带数字的字符串比较
数据库里面有:
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 dual
union all
select 'oracle2' name from dual
union all
select 'oracle45' name from dual
union all
select 'oracle9' name from dual)
select * from (select  t.name from t order by to_number(regexp_substr(t.name,'\d+')) desc) where rownum=1;