Порядок MySQL по строкам с числами

У меня есть строки, такие как M1 M3 M4 M14 M30 M40 и т.д. (на самом деле любые 2-3 цифры после буквы) Когда я делаю "ORDER BY name", это возвращает:

M1, M14, M3, M30, M4, M40

Когда я хочу:

M1, M3, M4, M14, M30, M40 Он обрабатывает все это как строку, но я хочу рассматривать ее как string + int

Любые идеи?

Ответы

Ответ 1

Вы можете использовать SUBSTR и CAST AS UNSIGNED/SIGNED в ORDER BY:

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

Ответ 2

Если в начале строки может быть несколько символов, например, как 'M10', 'MTR10', 'ABCD50', 'JL8', etc..., вам в основном нужно получить подстроку имени из первой позиции числа.

К сожалению, MySQL не поддерживает такую операцию REGEXP (возвращается только логическое значение, а не фактическое совпадение).

Вы можете использовать это решение для его эмулирования:

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

Это будет упорядочиваться символьной частью строки сначала, а затем выделенной числовой частью строки до тех пор, пока в начале строки не будет <= 7 символов. Если вам нужно больше, вы можете просто связать дополнительный WHEN с инструкцией CASE.

Ответ 3

Вы можете использовать:

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

Ответ 4

Я не мог заставить это работать для моей проблемы, которая сортировала номера MLS, как показано ниже:

V12345 V1000000 V92832

Проблема была V1000000 не оценивалась выше, чем остальные, хотя она больше.

Используя эту проблему, я решил:

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

Просто удалил SUBSTR(col_name FROM 1 FOR 1)

Ответ 5

Он разделяет число и буквы как отдельно.

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;

Ответ 6

Попробуйте удалить символ с помощью SUBSTR. Затем используйте ABS для получения абсолютного значения из поля:

SELECT * FROM table ORDER BY ABS(SUBSTR(field,1));