| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
Sorting a book index with SQL and PL/SQL | ||
|
This example shows how an index into chapters can be sorted.
Here's the table containing the chapter number and the title.
create table book_contents( chap_no varchar2(20), title varchar2(100) );
... going to fill the table ...
insert into book_contents values ('2.3', 'Homo homini lupus');
insert into book_contents values ('1.4', 'Carpe diem!');
insert into book_contents values ('1.4.4', 'Cum tempore');
insert into book_contents values ('1.4.1', 'Circulus vitiosus');
insert into book_contents values ('2.11', 'Persona non grata');
insert into book_contents values ('2.11.1', 'Cuius regio, eius religio');
insert into book_contents values ('1.4.3', 'Credo, ergo sum');
insert into book_contents values ('2.11.2', 'Primus inter pares');
insert into book_contents values ('1.7.1', 'Veni, vidi, vici');
insert into book_contents values ('1.7.2', 'Quis custodit custodes');
insert into book_contents values ('2.2.3', 'In memoriam perpetuam');
insert into book_contents values ('1.7.3', 'Divide et impera');
insert into book_contents values ('1.7.3.1', 'Anoo domini');
insert into book_contents values ('2.2.2', 'Variatio delectat');
insert into book_contents values ('2.6', 'Per noctern ad lucern');
insert into book_contents values ('1.7.4', 'Quod licet Iovi, non licet bovi');
insert into book_contents values ('1.7.5', 'In dubio pro reo');
insert into book_contents values ('1.7.8', 'Casus belli');
insert into book_contents values ('1.7.8.1', 'Beneficium accipere est libertatem vendere');
insert into book_contents values ('1.7.3.2', 'In vino veritas');
insert into book_contents values ('1.7.8.3', 'Fortes fortuna adiuvat');
insert into book_contents values ('1.7.9', 'Civis romanum sum');
insert into book_contents values ('2.2.1', 'Genius loci');
insert into book_contents values ('1.7.10', 'Manus manum lavat');
insert into book_contents values ('1.7.12', 'Ultima ratio');
insert into book_contents values ('3.2', 'Summa cum laude');
insert into book_contents values ('1.7.12.1', 'Alter Ego');
insert into book_contents values ('1.6', 'post festum');
insert into book_contents values ('1.7.13', 'Conditio sina qua non');
insert into book_contents values ('2.11.2.2', 'Semperit');
insert into book_contents values ('1.7.12.2', 'Bona Fide');
insert into book_contents values ('2.13', 'Vade mecum');
insert into book_contents values ('1.5', 'Ora et labora!');
insert into book_contents values ('2.8.1', 'Dum spiro, spero');
insert into book_contents values ('3', 'Plenus venter non studet libenter');
insert into book_contents values ('1.7.11', 'Sub ausspiciis (praesidentis rei publicae)');
insert into book_contents values ('1.4.2', 'Cogito, ergo sum');
insert into book_contents values ('1.7.7', 'Cum tempore');
insert into book_contents values ('1.12', 'Hic et nunc');
insert into book_contents values ('1.7.8.2', 'Carum est, quod rarum est');
insert into book_contents values ('1.7', 'Post scriptum');
insert into book_contents values ('2.11.3', 'Quod non est in actis, not est in mundo');
insert into book_contents values ('2.9', 'Urbi et orbi');
insert into book_contents values ('3.1', 'Sine ira et studio');
insert into book_contents values ('1.8', 'Erraro humanum est');
insert into book_contents values ('2.12', 'Panem et circenses');
insert into book_contents values ('1.7.6', 'Sine tempore');
insert into book_contents values ('1.9', 'Deus ex machina');
insert into book_contents values ('2.1', 'Medias res');
insert into book_contents values ('1.10', 'Hannibal ante portas');
insert into book_contents values ('1.11', 'Coram publico');
insert into book_contents values ('2.4', 'Lubus in fabula');
insert into book_contents values ('1.3', 'post meridiem');
insert into book_contents values ('2', 'In spe');
insert into book_contents values ('2.2', 'Dies diem docet');
insert into book_contents values ('1.2', 'ante meridiem');
insert into book_contents values ('2.5', 'Per aspera ad astra');
insert into book_contents values ('2.7', 'Post nubila phoebus');
insert into book_contents values ('2.8', 'Non scholae, sed vitae discumus');
insert into book_contents values ('2.11.2.1', 'Semper et ubique');
insert into book_contents values ('1', 'Ad multos annos!');
insert into book_contents values ('1.1', 'Alea iacta est');
insert into book_contents values ('2.8.2', 'Onmia vincit amor');
insert into book_contents values ('2.8.3', 'O tempora, o mores');
insert into book_contents values ('2.10', 'Pecunia non olet');
The following function is essential. It makes a chapter number into
something that is correctly sortable. For example, the chapter number
1.4.2 becomes 0001.0004.0002. A limitation of this
function is that it failes with chapter numbers that have more than
4 digits such as in 4.49094.50. However, such a chapter should be rather an
exception than the rule.
create or replace function real_chap (p_chap in varchar2)
return varchar2
as
ret varchar2(30);
pos_dot number;
last_pos_dot number := 0;
v_chap varchar2(30);
s_str varchar2( 5);
begin
v_chap := p_chap || '.';
pos_dot := instr(v_chap,'.',last_pos_dot+1);
while pos_dot > 0 loop
s_str := substr(v_chap,last_pos_dot+1,pos_dot-last_pos_dot-1);
ret := ret || lpad( s_str, 4, '0'); -- || ' - ';
last_pos_dot := pos_dot;
pos_dot := instr(v_chap,'.',pos_dot+1);
end loop;
return ret;
end;
/
Now, this function can be applied in the
select * from book_contents order by real_chap(chap_no); CHAP_NO TITLE -------------------- ---------------------------------------------------- 1 Ad multos annos! 1.1 Alea iacta est 1.2 ante meridiem 1.3 post meridiem 1.4 Carpe diem! 1.4.1 Circulus vitiosus 1.4.2 Cogito, ergo sum 1.4.3 Credo, ergo sum 1.4.4 Cum tempore 1.5 Ora et labora! 1.6 post festum 1.7 Post scriptum 1.7.1 Veni, vidi, vici 1.7.2 Quis custodit custodes 1.7.3 Divide et impera 1.7.3.1 Anoo domini 1.7.3.2 In vino veritas 1.7.4 Quod licet Iovi, non licet bovi 1.7.5 In dubio pro reo 1.7.6 Sine tempore 1.7.7 Cum tempore 1.7.8 Casus belli 1.7.8.1 Beneficium accipere est libertatem vendere 1.7.8.2 Carum est, quod rarum est 1.7.8.3 Fortes fortuna adiuvat 1.7.9 Civis romanum sum 1.7.10 Manus manum lavat 1.7.11 Sub ausspiciis (praesidentis rei publicae) 1.7.12 Ultima ratio 1.7.12.1 Alter Ego 1.7.12.2 Bona Fide 1.7.13 Conditio sina qua non 1.8 Erraro humanum est 1.9 Deus ex machina 1.10 Hannibal ante portas 1.11 Coram publico 1.12 Hic et nunc 2 In spe 2.1 Medias res 2.2 Dies diem docet 2.2.1 Genius loci 2.2.2 Variatio delectat 2.2.3 In memoriam perpetuam 2.3 Homo homini lupus 2.4 Lubus in fabula 2.5 Per aspera ad astra 2.6 Per noctern ad lucern 2.7 Post nubila phoebus 2.8 Non scholae, sed vitae discumus 2.8.1 Dum spiro, spero 2.8.2 Onmia vincit amor 2.8.3 O tempora, o mores 2.9 Urbi et orbi 2.10 Pecunia non olet 2.11 Persona non grata 2.11.1 Cuius regio, eius religio 2.11.2 Primus inter pares 2.11.2.1 Semper et ubique 2.11.2.2 Semperit 2.11.3 Quod non est in actis, not est in mundo 2.12 Panem et circenses 2.13 Vade mecum 3 Plenus venter non studet libenter 3.1 Sine ira et studio 3.2 Summa cum laude |