0

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.

user3161008
  • 1
  • 1
  • '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 Apr 20 '15 at 16:36

1 Answers1

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
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks, Alex. It does work well. However, when I extend it to include longer lists it seems to work not so well. – user3161008 Apr 29 '15 at 15:07
  • 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. – Alex Poole Apr 29 '15 at 15:16
  • 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 Apr 29 '15 at 15:18
  • 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... – user3161008 Apr 29 '15 at 15:24