MySQL order by string with numbers

Question

J'ai des chaînes de caractères telles que M1 M3 M4 M14 M30 M40 etc (en fait, n'importe quel chiffre à 2-3 chiffres après une lettre) Lorsque je fais " ORDER BY name ", j'obtiens un résultat :

M1, M14, M3, M30, M4, M40

Quand je veux :

M1, M3, M4, M14, M30, M40 Il traite le tout comme une chaîne de caractères, mais je veux le traiter comme une chaîne de caractères + int.

Des idées ?

Answer 1

Vous pouvez utiliser SUBSTR et CAST AS UNSIGNED/SIGNED dans ORDER BY :

SELECT * FROM table_name ORDER BY
    SUBSTR(col_name FROM 1 FOR 1),
    CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)

Answer 2

S'il peut y avoir plusieurs caractères au début de la chaîne, par exemple comme 'M10', 'MTR10', 'ABCD50', 'JL8', etc... vous devez essentiellement obtenir la sous-chaîne du nom à partir de la première position d'un nombre.

Malheureusement, MySQL ne prend pas en charge ce type d'opération REGEXP (seule une valeur booléenne est renvoyée, pas la correspondance réelle).

Vous pouvez utiliser cette solution pour l'émuler :

SELECT   name
FROM     tbl
ORDER BY CASE WHEN ASCII(SUBSTRING(name,1)) BETWEEN 48 AND 57 THEN
                   CAST(name AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,2)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,1)
              WHEN ASCII(SUBSTRING(name,3)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,2)
              WHEN ASCII(SUBSTRING(name,4)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,3)
              WHEN ASCII(SUBSTRING(name,5)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,4)
              WHEN ASCII(SUBSTRING(name,6)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,5)
              WHEN ASCII(SUBSTRING(name,7)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,6)
              WHEN ASCII(SUBSTRING(name,8)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,7)
         END,
         CASE WHEN ASCII(SUBSTRING(name,1)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,1) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,2)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,2) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,3)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,3) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,4)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,4) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,5)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,5) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,6)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,6) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,7)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,7) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,8)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,8) AS UNSIGNED)
         END

Le classement se fera d'abord par la partie caractère de la chaîne, puis par la partie numérotée extraite de la chaîne, à condition qu'il y ait <=7 caractères au début de la chaîne. Si vous en avez besoin de plus, vous pouvez simplement enchaîner d'autres WHEN à la CASE déclaration.

Answer 3

Je n'ai pas réussi à le faire fonctionner pour mon problème qui était de trier les numéros MLS comme ci-dessous :

V12345 V1000000 V92832

Le problème était que V1000000 n'était pas évalué plus haut que les autres, bien qu'il soit plus grand.

Utiliser ceci a résolu mon problème :

ORDER BY CAST(SUBSTR(col_name FROM 2) AS UNSIGNED) DESC

Je viens d'enlever le SUBSTR(col_name FROM 1 FOR 1)

Answer 4

Vous pouvez utiliser :

order by name,SUBSTRING(name,1,LENGTH(name)-1)

Answer 5

Il divise les chiffres et les lettres séparément.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(col,'1', 1), '2', 1), '3', 1), '4', 1), '5', 1), '6', 1)
, '7', 1), '8', 1), '9', 1), '0', 1) as new_col  
FROM table group by new_col;