Tri Alphanumérique Oracle 9i

Version imprimable

07/06/2012, 13h45
pazair16

Tri Alphanumérique Oracle 9i

Bonjour je souhaite faire un tri alphanumérique sur une colonne d'une table

Le résultat que j'attend est le suivant :

1
5
5E8
6
10
100
101
200E1
Blabla
Toto
Zorro

Donc on tri en ordre naturel sur une colonne alphanumérique.

Pour l'instant j'utilise un NLS_SORT que j'ai mis à FRENCH_M mais les chiffres sont triés de cette façon

1
10
100
2
20
200

Sur Oracle 9i le Regex est impossible car implémenté à la version 10, je pensé à un substr et un instr, mais je ne sais pas comment m'y prendre.

Merci
07/06/2012, 14h30
spdev666

Bonjour,

Ce que tu appel tri "naturel" ne l'est pas tant que cela :
pourquoi l'ordre suivant 5, 5E8, 6, 51 est correct et pas 5, 6, 51, 5E8 ?

Tu essayes de mélanger 2 règles de tri numérique et alphabétique.
Le tri réalisé par Oracle est bien alphabétique, "ab" est bien avant "b" --> "10" est aussi avant "2"

Pour arriver à ce que tu veux, tu peux essayer de formater ta chaine de caractères sur n positions avec LPAD (mais ca ne semble pas correspondre) ou calculer toi même un "rang" pour chaque chaine de caractere.
07/06/2012, 14h40
Pomalaix

Citation:

Envoyé par pazair16

...Donc on tri en ordre naturel ...

C'est peut-être naturel dans votre esprit, mais je trouve votre exemple incompréhensible.
D'une part, vous n'avez pas les mêmes valeurs en haut et en bas, et d'autre part, je ne vois pas quel sens ça a de mettre 5E8 entre 5 et 6.

Oups désolé, je n'avais pas vu la réponse précédente !

Ce sont des noms de rue que je veux classer, donc (1,2,3,10 ect....) j'avais eu la solution pour le faire sous postgres (Merci punkoff)
Code:

1 2 3 4 5 6 SELECT val, substring(val FROM '#"[0-9]*#"%' FOR '#') FROM tb1 ORDER BY case when substring(val FROM '#"[0-9]*#"%' FOR '#') > '' then substring(val FROM '#"[0-9]*#"%' FOR '#')::int else NULL end, val;
Pour résumer j'aimerai avoir les chiffre avant les lettres, et faire en sorte que les lettres soient triées en ordre "Naturel". Vous comptez comme ça vous ? :1, 10, 100, 2, 22, 200

Bref le problème est que je suis sur Oracle 9i donc les possibilités sont réduites, et je n'ai pas assez de recul sur le SQL de oracle pour pouvoir appréhender toutes ses possibilités.

Merci

Votre tri est complètement personnalisé, vous aviez eu une solution personnalisée sous PostGreSQL, il suffit donc de coder votre propre solution personnalisée sous Oracle.

Après cinq minutes de tests, ceci devrait faire l'affaire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
With TMP as
(
select '1' as col from dual union all
select '5'        from dual union all
select '5E8'      from dual union all
select '6'        from dual union all
select '10'       from dual union all
select '100'      from dual union all
select '101'      from dual union all
select '200E1'    from dual union all
select 'Blabla'   from dual union all
select 'Toto'     from dual union all
select 'Zorro'    from dual union all
select '2'        from dual union all
select '20'       from dual union all
select '200'      from dual
)
  select col
    from TMP
order by to_number(case col
                     when translate(col, '#' || translate(col, '#0123456789', '#'), '#')
                     then col
                     else substr(col, 1, instr(col, translate(col, '#0123456789', '#'))-1)
                   end) asc nulls last
       , col asc;
 
COL
-------
1
2
5
5E8
6
10
20
100
101
200
200E1
Blabla
Toto
Zorro

Les données que j'ai donné sont des données fictives, les vrais sont dans une table qui se trouve dans une colonne avec plus de 25000 lignes

Je ne comprend pas une partie de la requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH TMP AS
(
SELECT '1' AS col FROM dual union ALL
SELECT '5'        FROM dual union ALL
SELECT '5E8'      FROM dual union ALL
SELECT '6'        FROM dual union ALL
SELECT '10'       FROM dual union ALL
SELECT '100'      FROM dual union ALL
SELECT '101'      FROM dual union ALL
SELECT '200E1'    FROM dual union ALL
SELECT 'Blabla'   FROM dual union ALL
SELECT 'Toto'     FROM dual union ALL
SELECT 'Zorro'    FROM dual union ALL
SELECT '2'        FROM dual union ALL
SELECT '20'       FROM dual union ALL
SELECT '200'      FROM dual
)

Vous comprendrez bien que je n'ai pas les valeur Toto et Zorro dans mes tables.

il faudrais que je remplace cette partie par ?

Code:

1
2
3
4
5
 
WITH TMP AS 
(
Select Champ_a_trier as col FROM dual union ALL
)

Code:

FROM dual union ALL

Pouvez vous m'expliquer sa fonction ??

Dans l'exemple de Waldar, la parti suivante de la requete sert à "simuler" une table de données
Code:

1 2 3 WITH TMP AS (SELECT Champ_a_trier AS col FROM dual union ALL ...)
Ce qui t'intéresse, c'est cette partie :
Code:

1 2 3 4 5 6 7 8 9 SELECT col FROM TMP ORDER BY to_number(case col when translate(col, '#' || translate(col, '#0123456789', '#'), '#') then col else substr(col, 1, instr(col, translate(col, '#0123456789', '#'))-1) end) ASC nulls last , col ASC;
où TMP représente ta TABLE et COL représente la colonne à trier

Pour plus d'explication sur le WITH, voir ici : http://www.dba-oracle.com/t_sql99_with_clause.htm

08/06/2012, 10h18
pazair16

Merci a tous pour vos réponse, cela m'a grandement aidé.

J'ai ensuite rajouté des clauses dans le order by, pour ceux que ça interesse, je classe par une colonne en mettant les champs non vide en premier ce qui donne

Code:

case when champs_a_trier ='' then null else champs_a_trier END

Voila merci je passe en résolu.

Ce n'est pas bon.. un test ='' ou "= NULL" ne sera jamais vrai
Donc pour moi ce code ne sert à rien

Code:

case when champs_a_trier ='' then NULL else champs_a_trier END

Je n'ai pas compris ce que tu voulais faire exactement, mais il existe la clause NULLS FIRST/LAST dans le ORDERS BY
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT col FROM (SELECT NULL AS col FROM dual UNION ALL SELECT 1 FROM dual) ORDER BY col COL 1 <null> SELECT col FROM (SELECT NULL AS col FROM dual UNION ALL SELECT 1 FROM dual) ORDER BY col NULLS FIRST COL <null> 1