ORDER BY sur des varchars pour avoir : 1,2,3, ,10,11,12, ,AA,AB,AC [Résolu]

[Grulf]

Bonjour,

J'ai une table avec un champ de type varchar(10).
Ce champ représente un n° de BD et il peut contenir une valeur entière (1,2,3,...,10,11,12,...) ou une chaîne (HS pour hors-série par exemple).

Avec un ORDER BY classique, je les récupère dans cet ordre :
1,10,11,12,...,2,21,...,3,31,...,HS1,HS2,...
(normal, c'est un varchar)

Mais je les voudrais comme ça :
1,2,3,...,10,11,12,...,HS1,HS2,...

Comment faire ?

J'ai pensé à enregistrer mes valeurs en base ainsi : 01,02,03,...
Mais si je passe la barre des 100, le problème se reposera.
Et en plus j'ai pas trop envie de m'amuser à rajouter un 0 par-ci, ou retirer un 0 par-là selon les cas.

Merci

[CinePhil]

Quel est ton SGBD ?

Regarde du côté de CAST pour transformer les chaînes en nombres et du côté de CASE pour tester si c'est bien un nombre et ainsi les mettre en premier ordre de tri.

[StringBuilder]

Avec Oracle, tu peux créer la fonction :

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
 
CREATE OR REPLACE FUNCTION NATURAL_ORDER(
    P_STR   varchar2
) RETURN varchar2
IS
/** --------------------------------------------------------------------
    Replaces all sequences of numbers shorter than 10 digits by 0-padded
    numbers that exactly 10 digits in length. Usefull for ordering-by
    using NATURAL ORDER algorithm.
 */
    l_result  varchar2( 32700 );
    l_len     integer;
    l_ix      integer;
    l_end     integer;
begin
    l_result := P_STR;
    l_len := LENGTH( l_result );
    l_ix := 1;
    while l_len > 0 loop
        l_ix := REGEXP_INSTR( l_result, '[0-9]{1,9}', l_ix, 1, 0 );
        EXIT when l_ix = 0;
        l_end := REGEXP_INSTR( l_result, '[^0-9]|$', l_ix, 1, 0 );
        IF ( l_end - l_ix >= 10 ) then
            l_ix := l_end;
        else
            l_result := substr( l_result, 1, l_ix - 1 )
                     || LPAD( SUBSTR( l_result, l_ix, l_end-l_ix ), 10, '0' )
                     || substr( l_result, l_end )
                     ;
            l_ix := l_ix + 10;
        end IF;
    end loop;
    RETURN l_result;
end;
/

Et l'utiliser comme suit :

Code :

1
2
3
4
 
SELECT code
FROM bidule
ORDER BY natural_order(code);

Tu peux essayer de t'en inspirer.

[Grulf]

Merci, mais c'est vrai que j'ai oublié de dire que je suis sous MySQL.

Code :

ORDER BY CAST(bdt.NUMERO_TOME AS INT)

=> Crash

Code :

ORDER BY CAST(bdt.NUMERO_TOME AS SIGNED)

=> Tri : HS,1,2,...,10,11

Code :

ORDER BY IFNULL(CAST(bdt.NUMERO_TOME AS INT),bdt.NUMERO_TOME)

=> Crash

Code :

ORDER BY IFNULL(CAST(bdt.NUMERO_TOME AS SIGNED),bdt.NUMERO_TOME)

=> Tri : HS,1,10,...,2,21

Je regarde du côté de CASE.

[CinePhil]

Je ne sais pas si ça fonctionnerait avec d'autres SGBD mais il se trouve que sous MySQL, si tu divises une colonne par elle-même et que celle-ci a une valeur pouvant être traduite par un nombre, l'opération s'effectue ou donne NULL dans le cas contraire.
'4' / '4' => 1
'HS 1' / 'HS 1' => NULL

Donc avec cette requête qui donne une priorité aux divisions non nulles, tu obtiens le bon classement :

Code :

1
2
3
4
5
6
7
8
SELECT bd_id, bd_numero
FROM bede
ORDER BY 
    CASE 
        WHEN bd_numero/bd_numero IS NOT NULL THEN 1
        ELSE 2
    END,
    bd_numero

[StringBuilder]

Je pense tout de même qu'il serait plus intéressait de trouver comment faire un natural sort avec MySQL (ça doit être possible).

En effet, autant "4" est avant "HS 1", autant il est après "1 HS".

Idem pour trier "HS 1", "HS 10" et "HS "2".

Si vraiment MySQL ne le supporte pas, il faudrait peut-être penser à faire ce tri non pas dans le SGBD mais dans l'outils qui interroge le SGBD, mais niveau performances, ça peut vite devenir moyen, et surtout ça ne résoud pas tout les cas, notamment si tu en as besoin lors de traitements plus complexes en PL/SQL notamment.

[CinePhil]

Effectivement, il peut y avoir problème avec l'ordre HS 1, HS 10, HS 2.

Si tous les HS sont formés de la même manière (par exemple les lettres HS suivies d'un espace puis d'un numéro), alors on peut utiliser les fonctions de chaîne supprimer les caractères et trier les numéros.

Pas le temps d'étudier ça maintenant mais c'est l'idée.

[Grulf]

Merci pour vos réponses, je regarderai ça lundi.
Mais je pense que la solution de CinePhil devait me suffire. Les HS sont formés de la même manière, et il ne devrait pas y en avoir plus de 3 ou 4 pour chaque résultat de requête.
Je teste ça demain et je vous redis si j'arrive à en faire ce que je veux.
Merci

[dehorter olivier]

Une petite remarque (en passant).
Ne serait il pas judicieux (si c'est possible dans ton contexte, bien sur), d'avoir
* une colonne "Type" Integer
* une colonne "HS" analogue boolean

et une contrainte d'unicité sur ces 2 colonnes.


Comme cela ton tri serait

Code :

ORDER BY HS, Type

et te donnerait le résultat escompté.

Mais seulement si tu peux modifier la structure

[Grulf]

Effectivement, ça serait plus judicieux, vu que je peux modifier la structure.
Je vais partir là-dessus. Merci

[boussafi]

si tu n'as que des données de type 1,2,3...66..366.9999 et HS1,HS2..HS660 ou HS 1,HS 2...HS 654,...
voici la requête sous ORACLE

Code :

1
2
3
4
SELECT 
* FROM tab1 t
ORDER BY decode(LENGTH(TRIM(TRANSLATE (champ, ' +-.0123456789',' '))),NULL,to_number(champ)),
to_number(substr( champ,3,length(champ)))

le principe est simple sachant que l'on n'a que des 1,2,3...HS1,HS2,HS3....HS 1,HS 2,HS 3....:
1/ on fait le premier tri comme suit :
on teste si le champ est un numérique, généralement dans les autres SGBD on trouve la fonction is_numeric...isnumeric ou ..., puis on le convertit en numérique.
2/ deuxième tri : on tronque le champ à partir du troisième caractère en prenant la partie numérique, puis on convertit cette dernière en numérique.