сложная сортировка в order by

всем добра!
у меня тут есть каталог товаров, у каждого товара есть артикул, поле art . он выглядит примерно так 'S 147 C1', тип character(15) NOT NULL

мне нужно отсортировать так, чтобы и по буквам и по цифрам было. то есть

'S 10'
'S 140'
'S 147 A'
'S 147 C1'
'S 147 C2'
'S 147 C10'
'S 1000'

я пробовал

order by NULLIF(regexp_replace(art, E'\\D+(\\d+).*', '\\1', 'g'), '')::int

но это сортирует только во первому числу, а нужно по двум и по буквам :(
одно время артикул начинася с цифр, но сейчас все вроде начинаются с букв. еще есть вида [A-Z]+\.[0-9]+

как мне так отсортировать?

alexy_black,

попробуйте

order by mid(art,1,instr(art," ")) , val(mid(art,instr(art," ")) ,art

ПЕНСИОНЕРКА,

хм.. там с двойными ковычками пишет что

колонка " " не существует

а с одинарыми пишет

функция instr(character, unknown) не существует

и что мне, возможно нужно выполнить преобразования типов.

я вот переделал немного на постгресный синтаксис :) но он мне сказал, что никакого val не существует. оно там зачем было?

select id, art from elements where collection=165 order by substring(art,1,position(' ' in art)) , substring(art,position(' ' in art)) ,art;

код

select id, art from elements where collection=165 order by substring(art,1,position(' ' in art)) , cast(substring(art,position(' ' in art)) as integer) ,art;

не работает, говорит

ОШИБКА:  неверное значение для целого числа: " 803 A"

странно, та же проблема с

select id, art from elements where collection=165 order by substring(art,1,position(' ' in art)) , cast(substring(art,position(' ' in art),position(' ' in art)) as integer) ,art;

S 1000 и S 1000 M стоят перед S 115 C1

ПЕНСИОНЕРКА, спасибо за наводку

alexy_black,

я вам и давала наводку(из аксесс)
с постгресс никогда не работала

в лоб

WITH test (f ) AS
(values
('S 10')
,('S 140')
,('S 147 A')
,('S 147 C1')
,('S 147 C2')
,('S 147 C10')
,('S 1000')
)
,split (f,arr) AS (SELECT f, regexp_split_to_array(f,E'\\s+') AS arr  FROM test)
SELECT f,arr,arr[1] AS "1" ,arr[2]::int AS "2",regexp_replace(arr[3],'\d','','ig')  ,(NULLIF(regexp_replace(arr[3],'[[:alpha:]]','','ig'),''))::int
FROM split
ORDER BY 3,4,5,6

или можно сразу регуляризнуть примерно так

WITH test (f ) AS
(values
('S 10')
,('S 140')
,('S 147 A')
,('S 147 C1')
,('S 147 C2')
,('S 147 C10')
,('S 1000')
)
, split AS (SELECT f, regexp_split_to_array(regexp_replace(f,E'([[:alpha:]])(\\d+)','\1 \2','ig' ),E'\\s+') AS arr  FROM test)
SELECT * FROM split ORDER BY arr[1],arr[2]::int ,arr[3], arr[4]::int

чтобы особые случаи не рассматривать

alexy_black
но это сортирует только во первому числу, а нужно по двум и по буквам :( одно время артикул начинася с цифр, но сейчас все вроде начинаются с букв. еще есть вида [A-Z]+\.[0-9]+ как мне так отсортировать?

Вообще стандартное решение таких проблем в любой СУБД --

ORDER BY case when x1 = 'y1' then 1 when x1 = 'y2' then 2 else 2000 end

Таким макаром можно задавать вообще любую сортировку.
Можно CASE каскадировать (вкладывать друг в друга). Можно ранжировать (несколько выражений в ORDER BY).