Сортировка данных из текстового поля как числа?

Question

[Angelxalfa]

Здравствуйте!
Есть таблица с полями, к которых содержаться смешанные данные (цифры с буквами).
При получении данных из этой таблице нужно их сортировать как числа.
Раньше работал в связке с MySQL, но сейчас потребовалось перейти на Postgress
использовал запрос

select data from ".$prefix."$table WHERE pre_data = '$pre_data' ORDER BY (data+0)

В MySQL такой запрос проходил без проблем, но postgres дает ошибку.
Как можно осуществить желаемое в postgres?

В итоге решил через сортировку в массиве методами php

// Получение списка столбцов
foreach ($out2 as $key => $row) {
$volume[$key] = $row['data'];
}
//Сортировка
array_multisort($volume,SORT_NUMERIC, SORT_ASC, $out2);

Answer 1

[krypt3r]

Извлеките из столбца data все числовые данные, по которым нужно сортировать, в новый столбец числового типа и сортируйте по нему.

Comments

[Angelxalfa]

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

Answer 2

[He11ion]

SELECT * FROM unnest(array['1','2б','2а','1а','2']) a ORDER BY a ASC;

дает
1
1а
2
2а
2б

на посгресе 9.4.1

Comments

[Angelxalfa]

Неправильно написал пример...если взять такие номера, то будет некорректно
10
11
13/6
7
9
111
26а
3б

[He11ion]

Angelxalfa:
select * from unnest(array['10',
'11',
'13/6',
'7',
'9',
'111',
'26а',
'3б' ]) a
ORDER BY (substring(a, '^[0-9]+'))::int
,substring(a, '[^0-9_].*$');

"3б"
"7"
"9"
"10"
"11"
"13/6"
"26а"
"111"

[Angelxalfa]

Запрос:
select=house_number&table=$table&where=street = '".$street."'&order=(substring(house_number, '^[0-9]+'))::int,substring(house_number, '[^0-9_].*$')

отсортировано так:
12/13
9А
16
17
18
19
26
27
31
33
7
15
10
11
14

[Angelxalfa]

скажите, а можно как-нибуть преодбразовать тип данных при сортировки (чтобы postgres думал, что house_number это число, а не строка)?
Заранее спасибо!

[He11ion]

Angelxalfa:
"pre_data"::int - например

[Angelxalfa]

Не помогает...
В итоге решил через сортировку в массиве методами php
// Получение списка столбцов
foreach ($out2 as $key => $row) {
$volume[$key] = $row['house_number'];
}
//Сортировка
array_multisort($volume,SORT_NUMERIC, SORT_ASC, $out2);

Answer 3

[mnbck]

Для oracle работают вот такие приемы - www.techonthenet.com/oracle/questions/sort1.php
Для Postgre, думаю тоже должно подойти.

Answer 4

[sokollondon]

ORDER BY substring(column_name, '^[0-9]+')::int

Или так, чтобы после числа буквы сортировались по алфавиту

ORDER BY substring(column_name, '^[0-9]+')::int, substring(column_name, '[^0-9]*$')