Сортировать буквенно-цифровую колонку

У меня есть столбец в базе данных:

Serial Number
-------------
S1
S10
...
S2
S11
..
S13

Я хочу отсортировать и вернуть результат следующим образом для серийного номера <= 10:

S1
S2
S10

Один способ, которым я попробовал, был:

select Serial_number form table where Serial_Number IN ('S1', 'S2',... 'S10');

Это решает цель, но ищет лучший путь

Источник

3 ответа

Решение

Для Postgres вы можете использовать что-то вроде этого:

select serial_number
from the_table
order by regexp_replace(serial_number, '[^0-9]', '', 'g')::integer;

regexp_replace удалит все нечисловые символы, а результат будет обработан как число, подходящее для "правильной" сортировки.

Изменить 1:

Вы можете использовать новый "номер", чтобы ограничить результат запроса:

select serial_number
from (
  select serial_number, 
         regexp_replace(serial_number, '[^0-9]', '', 'g')::integer as snum
  from the_table
) t 
where snum <= 10
order by snum;

Редактировать 2

Если вы получили ошибку ERROR: invalid input syntax for integer: "" тогда, очевидно, у вас есть значения в столбце serial_number, которые не соответствуют формату, который вы опубликовали в своем вопросе. Это означает, что regexp_replace() удаляет все символы из строки, поэтому строка S вызвало бы это.

Чтобы предотвратить это, вам нужно либо исключить эти строки из результата, используя:

where length(regexp_replace(serial_number, '[^0-9]', '', 'g')) > 0

во внутреннем выборе. Или, если по каким-то причинам вам нужны эти строки, разберитесь с этим в списке выбора:

select serial_number
from (
  select serial_number, 
         case
            when length(regexp_replace(serial_number, '[^0-9]', '', 'g')) > 0 then regexp_replace(serial_number, '[^0-9]', '', 'g')::integer as snum
            else null -- or 0 whatever you need
         end as snum
  from the_table
) t 
where snum <= 10
order by snum;

Это действительно хороший пример того, почему вы никогда не должны смешивать две разные вещи в одном столбце. Если все ваши серийные номера имеют префикс S Вы не должны хранить его и поставить реальное число в реальный integer (или же bigint) столбец.

Используя что-то вроде NOT_SET указывать недостающее значение также является плохим выбором. NULL Значение было точно придумано по этой причине: для указания отсутствия данных.

Источник

Вот простой способ для этого формата:

order by length(Serial_Number),
         Serial_Number

Это работает, потому что префикс ('S') одинаковой длины для всех значений.

Источник

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

SELECT *
FROM   tbl
WHERE  right(serial_number, -1)::int < 11
ORDER  BY right(serial_number, -1)::int;

Требуется Postgres 9.1 или более поздняя версия. В старых версиях заменить substring (x, 10000),

Источник
Другие вопросы по тегам