Сортировка текстового поля как числа и как строки одновременно в MySQL

    В базе есть текстовое поле, данные из которого нужно вывести в алфавитном порядке, если данные текстовые, или отсортировать как числа, если данные представляют из себя числа.

    Пример требуемой сортировки: 8, 9, 10, 11, 12, Android 2.2, Android 2.3, BlackBerry

    Подобная сортировка мне понадобилась при разработке фильтра товаров для одного движка магазинов (к нему имею отношение, сочтите за рекламу), в котором могут быть как текстовые данные, так и числовые (а иногда и вместе одновременно, как в примере).

    Гугл навёл только на какие-то страшные триггеры и регекспы.
    Предлагаю вам предложить своё решение.

    Моё решение (выделите текст, чтобы подсмотреть):
    ORDER BY value=0, -value DESC, value
    Поделиться публикацией

    Похожие публикации

    Комментарии 18

      0
      Эм… А разве сортировка текста идёт не по кодам символов? Цифры так же обладают своим кодом и поэтому так же прекрасно отсортируются. Разве не так?
        +2
        Не совсем так. Если я правильно понял автора, то при сортировке по возрастанию строка «8» должна идти раньше строки «12», не смотря на то, что символ '8' имеет код больше чем '1' (первый символ строки «12»).
        +3
        Ваше решение, судя по всему, основывается на специфике(или же на баге) платформы базы данных, которую вы используете. А платформу вы не указываете.

        На моей платформе данный подход неизбежно приводит к ORA-01722: invalid number
        +2
        Укажите, пожалуйста, в какой СУБД работает указанное решение.

        А ещё желательно объяснить, почему именно такие условия сортировки и именно в таком порядке,
        что даёт каждое условие и почему.

        А то похоже на решение из книги «100 SQL запросов для чайников».
          0
          Не знаю, как в других БД, но в MySQL ваше решение некорректно. Например, таблица из элементов «8», «9», «2.2 Android», «2.3 Android» сортируется как «2.2 Android», «2.3 Android», «8», «9», хотя, насколько я понимаю задание, «8» и «9» должны быть первыми.
            –1
            Ну. На вскидку, нужен еще один столбец с числовым значением. Его можно сразу генерить во время insert в таблицу, но у меня сейчас нет времени писать правильный create table, так что как-то так:
              0
              Блин. Пост порвало. При попытке повторной отправки вообще все виснет. Вот в первом попавшемся G+ написал
              Но все равно решение надо допиливать, особенно после выхода Android 10 :)
              +1
              Боюсь вас расстроить, но в MySQL ваш запрос будет работать не правильно:
              SELECT *, value=0 FROM
              (
              	SELECT '1' AS VALUE UNION
              	SELECT '03' AS VALUE UNION
              	SELECT '10' AS VALUE UNION
              	SELECT '1.1' AS VALUE UNION
              	SELECT 'z' AS VALUE UNION
              	SELECT '1a' AS VALUE UNION
              	SELECT '2' AS VALUE
              ) AS t ORDER BY value=0, -value DESC, value
              


              результат будет:
              1, 1a,…
                0
                мой вариант для MySQL:
                 SELECT * FROM
                (
                	SELECT '1' AS VALUE UNION
                	SELECT '03' AS VALUE UNION
                	SELECT '10' AS VALUE UNION
                	SELECT '1.1' AS VALUE UNION
                	SELECT '0..1' AS VALUE UNION
                	SELECT 'z' AS VALUE UNION
                	SELECT '1z' AS VALUE UNION
                	SELECT '2' AS VALUE
                ) AS t ORDER BY 
                	value REGEXP '^-?[0-9\.]+$' AND LENGTH(value) - LENGTH(REPLACE(value, '.', '')) < 2 DESC,
                    CAST(value AS UNSIGNED), value;
                

                0
                но за попытку — зачёт
                  0
                  На MS SQL вполне решается «в лоб», думаю на большинстве других СУБД тоже. Непонятно почему гугл откопал только регулярные выражения и триггеры. Так-то можно и триггером, от условий зависит.

                  declare @t table(name varchar(50));
                  insert @t values ('12'); insert @t values ('6');
                  insert @t values ('Android 2.2'); insert @t values ('Android 2.3'); insert @t values ('BlackBerry');
                  
                  select name from @t 
                  order by case when isnumeric(name) = 0 then name else '' end,
                  	case when isnumeric(name) = 0 then -1 else cast(name as int) end
                  
                    0
                    Если MySQL, то да, там isnumeric нет, то есть сортировка в моём варианте будет выглядеть так (а уж насколько хорошо для MySQL — я не настолько его знаю):
                    case when name REGEXP '^-?[0-9]+$' then '' else name end,
                    case when name REGEXP '^-?[0-9]+$' then cast(name as signed) else -1 end
                      0
                      MySQL особым образом выполняет преобразование строки в число, эту особенность весьма ловко использовал автор в своем решении. Ссылку на демонстрацию этой особенности в документации он оставил в коментариях.
                        0
                        Есть мнение, что эта особенность не решает поставленную задачу, что уже отмечено в комментарии выше.
                        0
                        Я бы сэмулировал isnumeric() при помощи, скажем, CONCAT(value+0)=value. Интересно, что проверка value=value+0 всегда истинна, даже если value='String'. Важно перед сравнением явно обратно привести тип к строке при помощи CONCAT().
                      0
                      Oracle:
                      OREDER BY to_number(regexp_substr(value,'^[0-9]+')),
                                         to_number(regexp_substr(value,'\$[0-9]+')),
                                         value
                      
                        0
                        А вторым регекспсубстром вы что хотели сделать?

                        SQL> select to_number(regexp_substr('$1','\$[0-9]+')) from dual
                        ORA-01722: invalid number

                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                      Самое читаемое