如何在Oracle中获得自然数字排序顺序

Question

我有一列，后面跟着一个字母，后面跟着数字或字母：

ID_Col
------
S001
S1001
S090
SV911
SV800
Sfoofo
Szap
Sbart

如何自然排序，先用数字(ASC(，然后按字母顺序排列？如果以S开头，其余字符为数字，则按数字排序。否则，按字母排序。所以SV911应该和字母一起排序在末尾，因为它也包含一个V。例如

ID_Col
------
S001
S090
S1001
Sbart
Sfoofo
SV800
SV911
Szap

我看到这个解决方案使用正则表达式和TO_NUMBER函数相结合，但由于我也有没有数字的条目，这似乎对我不起作用

ORDER BY 
TO_NUMBER(REGEXP_SUBSTR(ID_Col, '^Sd+$')), 
ID_Col 
/* gives ORA-01722: invalid number */

Answer 1

这有帮助吗？

SQL> with test (col) as
2  (select 'S001'   from dual union all
3   select 'S1001'  from dual union all
4   select 'S090'   from dual union all
5   select 'SV911'  from dual union all
6   select 'SV800'  from dual union all
7   select 'Sfoofo' from dual union all
8   select 'Szap'   from dual union all
9   select 'Sbart'  from dual
10  )
11  select col
12  from test
13  order by substr(col, 1, 1),
14    case when regexp_like(col, '^[[:alpha:]]d') then to_number(regexp_substr(col, 'd+$')) end,
15    substr(col, 2);
COL
------
S001
S090
S1001
Sbart
Sfoofo
SV800
SV911
Szap
8 rows selected.
SQL>