Discussion :

[pazair16]

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

[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.

[Pomalaix]

...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 !

[pazair16]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Waldar]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[pazair16]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FROM dual union ALL

Pouvez vous m'expliquer sa fonction ??

[spdev666]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

case when champs_a_trier ='' then null else champs_a_trier END

Voila merci je passe en résolu.

[McM]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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