在Oracle 11g中对字母数字字符串进行排序 [英] sorting alpanumeric strings in Oracle 11g

问题描述

我有一个客户需要有关以严格的字母数字顺序排序的Lawson令牌的报告. 以下是示例测试文件和我一直使用的查询，但均未成功.我不确定为什么regex_replace在这里不能正常工作. 我将不胜感激.

I have a client that needs a report on Lawson tokens sorted in a strict alphanumeric order. The following is a sample test file and the query I have been using without success. I am not sure why the regex_replace is not working well here. I will appreciate any help I can get.

谢谢.

enter code here

create table sortest (token varchar2(6));

insert into sortest values ("BR00.1');
insert into sortest values ("BRFL.1');
insert into sortest values ("BRBF.1');
insert into sortest values ("BR00.2');
insert into sortest values ("BRRF.1');
insert into sortest values ("BRIP.1');
insert into sortest values ("BRRF.3');
insert into sortest values ("BR00.3');
insert into sortest values ("BRBF.2'); 
insert into sortest values ("BRRF.2');
insert into sortest values ("BR01.2');
insert into sortest values ("BR06.1');
insert into sortest values ("BR01.1');
insert into sortest values ("BR17.1');
insert into sortest values ("BR132');
insert into sortest values ("BR120');
insert into sortest values ("BR12.1');
insert into sortest values ("BR121');
insert into sortest values ("BR13.2');

commit;

select * from sortest
--order by token
order by to_number(nvl(trim(regexp_replace(token,'[A-Za-z]')),0)) asc
;

这将返回"..... BR06.1，BR12.1，BR120，BR121，BR13.2，BR132，BR17.1 ..."等.顺序应输入BR13.2和BR17.1例如在BR120和BR121之前.

This returns '.....BR06.1, BR12.1, BR120, BR121, BR13.2, BR132, BR17.1....' etc. The order should put BR13.2 and BR17.1 before BR120 and BR121 for instance.

推荐答案

问题顺序中显示的内容仅是数值的数字部分，因此您将看到类似的内容:

What you've shown in the question orders just by the numeric part of the value, so you'll see something like:

TOKEN
------
BRIP.1
BRFL.1
BRBF.1
BR00.1
BRRF.1
BR00.2
BRRF.2
BRBF.2
BR00.3
BRRF.3
BR01.1
BR01.2
BR06.1
BR12.1
BR13.2
BR17.1
BR120 
BR121 
BR132 

如果要按字母字符排序，然后按其中的数字排序，则可以在order by子句中使用两个表达式-因此，您要按第一个字母部分排序，然后再按后面的数字组成除去所有字母字符:

If you want to order by the alphabetic characters and then by the numbers within those, you could use two expressions in the order by clause - so you order by the first alphabetic section, and then by the number formed from what's left after all alphabetic characters are stripped out:

select * from sortest
order by regexp_substr(token, '[[:alpha:]]*'),
  to_number(regexp_replace(token, '[[:alpha:]]', null));

TOKEN
------
BR00.1
BR00.2
BR00.3
BR01.1
BR01.2
BR06.1
BR12.1
BR13.2
BR17.1
BR120 
BR121 
BR132 
BRBF.1
BRBF.2
BRFL.1
BRIP.1
BRRF.1
BRRF.2
BRRF.3

这篇关于在Oracle 11g中对字母数字字符串进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！