如何在oracle中对字母数字字符串进行排序？

问题描述：

Section1 
Section2 
Section3 
Section10 
Section11 
Section1A 
Section1B 
Section12 
Section11A 
Section11B

我希望输出这样的：

Section1 
Section1A 
Section1B 
Section2 
Section3 
Section10 
Section11 
Section11A 
Section11B 
Section12

我试着查询：

select section_name 
from sections 
order by length(section_name),section_name

也许这可以帮助你：http://stackoverflow.com/questions/40342049/how-to-use-order-by-in-alphanumeric-column-in-oracle?noredirect=1&lq=1 –

你试过了吗？什么？ – Aleksej

我试过查询：选择section_name从部分按长度排序（section_name），section_name –

答

假设你的字符串的结构在你的榜样固定，，这可能是一种方式：

SQL> select x, 
    2   to_number(regexp_substr(x, '[0-9]+')) numericPart, 
    3   regexp_substr(x, '([0-9]+)([A-Z])', 1, 1, '', 2) optionalChar 
    4 from (
    5   select 'Section1' x from dual union all 
    6   select 'Section2' from dual union all 
    7   select 'Section3' from dual union all 
    8   select 'Section10' from dual union all 
    9   select 'Section11' from dual union all 
10   select 'Section1A' from dual union all 
11   select 'Section1B' from dual union all 
12   select 'Section12' from dual union all 
13   select 'Section11A' from dual union all 
14   select 'Section11B' from dual 
15 ) 
16 order by numericPart, 
17   optionalChar nulls first 
18 ; 

X   NUMERICPART OPTIONALCHAR 
---------- ----------- ---------------------------------------- 
Section1    1 
Section1A   1 A 
Section1B   1 B 
Section2    2 
Section3    3 
Section10   10 
Section11   11 
Section11A   11 A 
Section11B   11 B 
Section12   12

这里您首先按数字部分进行排序，将其视为数字，然后考虑数字后面的（可选）字符。

如何在oracle中对字母数字字符串进行排序？

相关推荐