17,089
社区成员
发帖
与我相关
我的任务
分享
1 with a as(select '西直门北大街58号7号楼1001' col1 from dual
2 union all
3 select '西直门北大街58号7号楼102' from dual
4 union all
5 select '西直门北大街59号8号楼10-1002' from dual
6 union all
7 select '西直门北大街59号8号楼1-103' from dual
8 )
9 select col1 from a
10* order by to_number(substr(replace(col1,'-',''),instr(col1,'楼')-length(repl
ace(col1,'-',''))))
SQL> /
COL1
----------------------------
西直门北大街58号7号楼102
西直门北大街58号7号楼1001
西直门北大街59号8号楼1-103
西直门北大街59号8号楼10-1002