oracle中混合数字与字符的字符串排序

 

(1)使用translate,replace方法

select qb.box_code from qc_box qb where qb.type_id = '402888e53d429ff5013d42ce99f4002f' 
ORDER BY   REPLACE(TRANSLATE(qb.box_code,'0123456789', '##########'), '#', '')||
       TO_CHAR(TO_NUMBER(REPLACE(qb.box_code,  REPLACE(TRANSLATE(qb.box_code, '0123456789', '##########'),
                                 '#', ''), '') ),'0009999');

 

 

 

(2)使用regexp_substr方法
 select  qb.box_code from qc_box qb where qb.type_id = '402888e53d429ff5013d42ce99f4002f'    order by  regexp_substr( qb.box_code,'[^0-9]+'),to_number(regexp_substr( qb.box_code,'[0-9]+'));