Заказать строку varchar как числовую

Ответ 1

Это абсолютно возможно.

ORDER BY varchar_column::int

Обязательно иметь действительные целочисленные литералы в столбце varchar или получить исключение. (Верхнее и конечное белое пространство в порядке - оно будет обрезано автоматически.)

Если это случай, то почему бы не преобразовать столбец в integer для начала? Меньше, быстрее, чище, проще.

Как избежать исключений?

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

ORDER BY NULLIF(regexp_replace(varchar_column, '\D', '', 'g'), '')::int

• regexp_replace() выражение эффективно удаляет все нецифровые символы, поэтому остаются только цифры или пустая строка. (См. Ниже.)

• \D является сокращением для символьного класса [^[:digit:]], что означает все не цифры ([^0-9]).
  В старых версиях Postgres с устаревшей настройкой standard_conforming_strings = off вам нужно использовать синтаксис строки escape-кода Posix E'\\D', чтобы избежать обратного слэша \. Это было по умолчанию в Postgres 8.3, поэтому вам понадобится эта устаревшая версия.

• 4-й параметр g предназначен для "глобально", указывая на замену всех вхождений, а не только на первый.

• Для отрицательных чисел вы можете разрешить ведущую тире (-).

• Если строка не имеет цифр вообще, результатом является пустая строка, которая недопустима для трансляции в integer. Преобразуйте пустые строки в NULL с помощью NULLIF. (Вместо этого вы можете рассмотреть 0.)

Результат гарантирован. Эта процедура предназначена для трансляции integer по запросу в теле вопроса, а не для numeric, как указано в названии.

Как быстро сделать это?

Один из способов - это индекс для выражения. (Ссылка на ручную версию 8.3.)

CREATE INDEX tbl_varchar_col2int_idx ON tbl
(cast(NULLIF(regexp_replace(varchar_column, E'\\D', '', 'g'), '') AS integer));

Затем используйте те же выражения в предложении ORDER BY:

ORDER BY
cast(NULLIF(regexp_replace(varchar_column, E'\\D', '', 'g'), '') AS integer)

Протестируйте с помощью EXPLAIN ANALYZE, действительно ли используется функциональный индекс.

Ответ 2

Также в случае, если вы хотите заказать текстовый столбец, который имеет что-то конвертируемое для float, тогда он делает следующее:

select * 
from your_table
order by cast(your_text_column as double precision) desc;