oracle - 使用函数操作进行 Oracle 排序

Question

为什么 oracle 没有正确排序或者我缺少什么？

select a from (
    select '100##' as a from dual union 
    select '43##Y' as a from dual union 
    select '58##Y' as a from dual union 
    select '78##' as a from dual union 
    select '##' as a from dual union 
    select '##' as a from dual
) order by to_number(substr(a, 0, instr(a, '#') - 1)) asc; 

输出 ：

43##Y
58##Y
78##
100##
##

标签： oraclesortingplsql

Answer 1

您需要将起始位置的 0 替换为 1，substr因为 oracle 中的索引从 1 开始。

select a from (
    select '100##' as a from dual union 
    select '43##Y' as a from dual union 
    select '58##Y' as a from dual union 
    select '78##' as a from dual union 
    select '##' as a from dual union 
    select '##' as a from dual
) order by to_number(substr(a, 1, instr(a, '#') - 1)) asc; 

Db<>小提琴