I have to sort an alphanumeric column as if it were separate numeric and alpha values in an Oracle database using Hibernate. Here is the example using Oracle SQL.
Given this table...
Code:
CREATE TABLE code_list (code VARCHAR2(6));
INSERT INTO code_list VALUES ('1');
INSERT INTO code_list VALUES ('10');
INSERT INTO code_list VALUES ('100');
INSERT INTO code_list VALUES ('C1');
INSERT INTO code_list VALUES ('2');
INSERT INTO code_list VALUES ('3');
INSERT INTO code_list VALUES ('4');
INSERT INTO code_list VALUES ('C3');
INSERT INTO code_list VALUES ('F3');
INSERT INTO code_list VALUES ('F1');
INSERT INTO code_list VALUES ('F17');
INSERT INTO code_list VALUES ('C9');
INSERT INTO code_list VALUES ('C10');
INSERT INTO code_list VALUES ('C47');
INSERT INTO code_list VALUES ('C100');
INSERT INTO code_list VALUES ('C134');
INSERT INTO code_list VALUES ('A234');
INSERT INTO code_list VALUES ('C1245');
INSERT INTO code_list VALUES ('AU89');
INSERT INTO code_list VALUES ('BB111');
INSERT INTO code_list VALUES ('B4111');
INSERT INTO code_list VALUES ('BR111');
INSERT INTO code_list VALUES ('BB20');
INSERT INTO code_list VALUES ('ZX23');
INSERT INTO code_list VALUES ('ZX456');
INSERT INTO code_list VALUES ('Z16');
INSERT INTO code_list VALUES ('Z66');
INSERT INTO code_list VALUES ('ZX5');
INSERT INTO code_list VALUES ('ABCD12');
INSERT INTO code_list VALUES ('RRR-12');
INSERT INTO code_list VALUES ('A1B2C3');
INSERT INTO code_list VALUES ('A1B55');
INSERT INTO code_list VALUES ('A1B1');
INSERT INTO code_list VALUES ('A1B2C1');
INSERT INTO code_list VALUES ('M2-40');
COMMIT;
This query...
Code:
select
code
from
code_list
order by
regexp_substr(code,'^\D+') nulls first,
to_number(regexp_substr(code,'\d+')),
to_number(regexp_substr(code,'\d+',1,2)) nulls last,
regexp_substr(code,'[[:alpha:]]+',1,2) nulls last,
to_number(regexp_substr(code,'\d+',1,3)) nulls last;
Returns the proper sort...
Code:
1
2
3
4
10
100
A1B1
A1B2C1
A1B2C3
A1B55
A234
ABCD12
AU89
B4111
BB20
BB111
BR111
C1
C3
C9
C10
C47
C100
C134
C1245
F1
F3
F17
M2-40
RRR-12
Z16
Z66
ZX5
ZX23
ZX456
But Oracle is abstracted behind the ORM, Hibernate, and I can't see how I may similarly chop up the sort column using the CriteriaBuilder nor how to utilized the "null first" or "null last" features.
Am I missing it, or is there a clever way?
Login
FAQ
Search
