sorting alpanumeric strings in Oracle 11g

Question

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.

Thanks.

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
;

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.

'order by token' gives the list you said is wrong; 'order by to_number...' seems to give you what you want, at least for the BR13.2, BR17.1, etc. Can you show more clearly what output you actually want, and the logic behind it? — Alex Poole
– Alex Poole, Commented Apr 20, 2015 at 16:36

Alex Poole · Accepted Answer · 2015-04-20 16:43:21Z

1

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

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

answered Apr 20, 2015 at 16:43

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3161008 Over a year ago

Thanks, Alex. It does work well. However, when I extend it to include longer lists it seems to work not so well.

Alex Poole Over a year ago

The length of the list shouldn't matter, but maybe you have different formats you need to handle. You'll need to update the question to show some more sample values and the order you want them in, if that is the case.

user3161008 Over a year ago

My apologies, I hit enter before I finished my thought....If I include some tokens like PISHC, PIREV, PIGTS that do have a numeric in them for some reason, they are not sorted in ASC order. Additionally, it seems like the sort order is by the numeric extension on some of the extra entries I made. Here are the extra entries: PIREV,PIGTS,PISHC,ACDL.1,ACSB.1 ACDM.1,ACBA.1,ACBB.1,ACDR.1,ACVW.1,ACAC.1,ATVW.1,ACBR.1,ACBY.1,ACSS.2,ACTD.2,AC00.3,ACFL.3,ACTD.3,ACBB.3,AC00.4,ACBB.4,ACFL.4,ACDM.4. Thanks a lot for your help. Maalim

user3161008 Over a year ago

The order should be something like: ACAC.1,ACBA.1,ACBB.1,ACBB.3, ACBB.4,ACBR.1,ACBY.1,ACDM.1,ACDM.4,ACDR.1,ACFL.3, etc...

Collectives™ on Stack Overflow

sorting alpanumeric strings in Oracle 11g

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related