这个用正则就好了吧,比如WITH r AS
(SELECT '1.1' x
FROM dual
UNION ALL
SELECT '1.2'
FROM dual
UNION ALL
SELECT '2'
FROM dual
UNION ALL
SELECT '2.1'
FROM dual
UNION ALL
SELECT '11.0.1'
FROM dual)
SELECT *
FROM r
ORDER BY nvl(0 + regexp_substr(x, '\d+', 1),-1),
nvl(0 + regexp_substr(x, '\d+', 2),-1),
nvl(0 + regexp_substr(x, '\d+', 3),-1),
nvl(0 + regexp_substr(x, '\d+', 4),-1)
(SELECT '1.1' x
FROM dual
UNION ALL
SELECT '1.2'
FROM dual
UNION ALL
SELECT '2'
FROM dual
UNION ALL
SELECT '2.1'
FROM dual
UNION ALL
SELECT '11.0.1'
FROM dual)
SELECT *
FROM r
ORDER BY nvl(0 + regexp_substr(x, '\d+', 1),-1),
nvl(0 + regexp_substr(x, '\d+', 2),-1),
nvl(0 + regexp_substr(x, '\d+', 3),-1),
nvl(0 + regexp_substr(x, '\d+', 4),-1)
解决方案 »
- create table 判断表存在不存在也重新创建的问题?
- oracle9i和oracle10g能装在同一部机上吗?
- 求求求!! 一个关于数据库查询的问题
- Oracle9中UTF-8转SIMPLIFIED CHINESE_CHINA.ZHS16GBK
- 同一台服务器上装有两个ORACLE数据库实例,通过命令启停数据库,如何区分操作的是哪个数据库实例
- oracle 全文检索问题(急)高分
- 请教一个 自增列插入新表的问题
- 关于两个连接方式的区别(conn user/pass 与conn user/pass@SID)。
- win2000 server装完oracle817.但服务里根本没有OracleManagementServer?
- 各位,我在java中连接oracle数据库,出现了点问题,请问是什么原因,谢谢!
- '1,2,3,4' 转number类型!急!
- tnsnames.ora中的LISTENER_ORCL是做什么用?
WITH r AS
(SELECT '1.1' x
FROM dual
UNION ALL
SELECT '1.2'
FROM dual
UNION ALL
SELECT '2'
FROM dual
UNION ALL
SELECT '2.1'
FROM dual
UNION ALL
SELECT '11.0.1'
FROM dual)
SELECT *
FROM r
ORDER BY nvl(0 + regexp_substr(x, '\d+', 1,1),-1),
nvl(0 + regexp_substr(x, '\d+', 1,2),-1),
nvl(0 + regexp_substr(x, '\d+', 1,3),-1),
nvl(0 + regexp_substr(x, '\d+', 1,4),-1)