Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

сортировка по столбцу character varying(10) как по integer

Posted Апрель 26th, 2009 by pwlad
in

Выполнение запроса:
select a1.physicalpersonid, a1.name,
(a3.streetname || ', ' || a2.buildingno || ' - ' || a1.flatno) as "fulladress"
from physpers a1, building a2, streets a3
where a1.buildingid=a2.buildingid
and a2.streetid=a3.streetid
and a1.buildingid = :buildingid
order by a1.flatno
где flatno character varying(10), приводит к сортировке 1, 10, 11, 12, 2, 21, 23, 3, 31 ... - что понятно.
В этом поле в основном целые значения, но не все (могут быть 2а, 7/1, сек.3кв.5 - и еще бог знает что).
Если делать a1.flatno::int то эти значения не прокатывают.
Подскажите, плз, способ отсортировать те значения, которые переводятся в числа, как числа, остальные как строка (надеюсь это возможно в одном запросе или функции PL/pgSQL).

‹ автоматическое приведение типов FOREIGN KEY на две таблицы ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

  SELECT

On Апрель 26th, 2009 msr says:

 
SELECT to_number(t.h,'99') AS n,t.h FROM 
(
SELECT '2' AS h UNION
SELECT '5a' UNION
SELECT '15rd56' UNION
SELECT '9/6'
) t
ORDER BY to_number(t.h,'99')
 
n  	h
2      2
5      5a
9      9/6
15     15rd56

можете создать и свою функцию перевода строки в число и даже сделать по этому выражению индекс

большое спасибо

On Апрель 27th, 2009 pwlad says:

Форматирование по маске?
Большое спасибо. В эту сторону как-то не посмотрел.

увы

On Апрель 27th, 2009 pwlad says:

Есть некоторые, обходимые в определенной степени, минусы такого способа, но:
Увы, если строка начинается с букв кириллицы ('сек.5кв.3'), то:
ERROR: неверный входной синтаксис для типа numeric: " "
SQL state: 22P02
Может быть есть способ, например, с помощью обработки исключений, разделить данные, приводимые к int и не приводимые, и производить сортировку отдельно для каждого набора?
Заранее благодарен.

последовал своему совету

On Апрель 27th, 2009 pwlad says:

Спасибо всем принявшим в проблеме участие.
Реализовал задумку через обработку исключений в pgsql-функции.

есть еще свои

On Апрель 27th, 2009 msr says:

есть еще свои особенности у функции to_number (на мой вкус, не очень удобные в данном случае)

SELECT to_number('5 к98','9') --5
SELECT to_number('5 к98','99')--5
SELECT to_number('5 к98','999')--5 
SELECT to_number('5 к98','9999') --59
SELECT to_number('5 к98','99999')--598 
 
SELECT to_number('58 к8','999')--58

точно

On Апрель 27th, 2009 pwlad says:

Да, именно это я и имел в виду в первой строчке предпредыдущего ответа.
Созданная мной функция pgsql с учетом создания типа для вывода результирующего набора и радующим глаз форматированием заняла 26 строк.
Не стал её приводить здесь, поскольку не универсальна, но суть такова:
1.создаю курсор, где кроме строкового значения, выбираю 0. открываю его.
2.делаю построчную выборку в цикле по переменной.
3.в защищенном блоке делаю приведение строкового значения к int с присвоением значения зарезервированному для этих целей поля (помните 0 ?).
4.возвращаю строчку в результат функции.
5.после цикла закрываю курсор.
В результате имею не сортированные данные, но наряду со строковым значением - числовое, вместо ошибки 0.
Далее простым запросом выбираю данные без числового поля, но отсортированные по нему.
Можно вместо 0 ставить 999999, если хотите разместить не преобразуемые значения после преобразуемых.

может в эту

On Апрель 27th, 2009 msr says:

может в эту сторону посмотреть

SELECT regexp_split_to_array('f56 4h6hj','[a-zA-Z ]') --{"",56,4,6,"",""}

решение описано в моих предыдущих постах

On Апрель 27th, 2009 pwlad says:

Спасибо за участие.
Как я уже писал, реализовал необходимый функционал через обработку исключений.
Подробности в предыдущих моих ответах.

очередное спасибо за наводку

On Апрель 30th, 2009 pwlad says:

Раньше не приходилось использовать функции регулярных выражений в SQL, но идея заслуживала внимание.
В результате решил задачу написанием sql-функции, правда использовал не regexp_split_to_array, а сочетание regexp_matches - regexp_replace.
Даже лучше получилось, чем при использовании исключений. Теперь отдельная сортировка идет не по всем данным, которые не приводятся к int, а только начинающиеся с символьных литералов.
Т.е. имеем отсортированный список вида:
секц.1 кв.1, секц.1 кв.2, 1, 2, 5, 5/1, 5а, 6, 11, 12 ...
Это оптимально для моей задачи.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023