Сортировка текстовых строк в SQL

Как такая сортировка называется?

https://en.wikipedia.org/wiki/Natural_sort_order

Можно ли это сделать средствами SQL?

Да

Можно ли это сделать средствами SQL?

Да

Даже в sqlite3?

Даже в sqlite3?

Да, определяете свой natural collation через sqlite3_create_collation и потом используете.

https://en.wikipedia.org/wiki/Natural_sort_order

Спасибо.

Для sqlite3 пока нашёл только советы, как писать расширение на Си.

Так это и есть ответ. sqlite3 это не standalone база, это библиотека для встраивания в твои программы.

В крсйнем случае ты всегда можешь отрезать scan/ если они одинаковы для всех.

sqlite3 это не standalone база, это библиотека для встраивания в твои программы.

Я имею в виду бинарник sqlite3.

Этот бинарник исключительно для вспомогательных/отладочных целей, ну или можешь считать его демкой как использовать библиотеку. Основной функционал sqlite - именно библиотека.

Ну и в исходник бинарника тоже можно дописать определение сортировки.

ORDER BY NULLIF(regexp_replace(file, '^scan/(\d+)\.txt$', '(\1)', 'g'), '')::int

Что-то в таком духе.

отрезать scan/ если они одинаковы для всех.

Этого оказалось недостаточно. Нужно обрезать весь текст и преобразовать в числа, иначе всё равно сортирует 79-8-80.

Но оказалось, в sqlite есть CAST.

Рабочий вариант:SELECT * FROM files ORDER BY CAST(REPLACE(SUBSTR(file, 6), '.txt', '') AS int);

Спасибо.

Тогда заодно связанный вопрос: почему в обеих базах работает
SELECT *, CAST(REPLACE(SUBSTR(file, 6), '.txt', '') AS int) AS b FROM files ORDER BY b;
но
SELECT *, REPLACE(SUBSTR(file, 6), '.txt', '') AS b FROM files ORDER BY CAST(b as int);
работает в SQLite, но в PostgreSQL выдаёт ошибку «column «b» does not exist»?

Сходу не отвечу, смотри различия синтакстса. Можешь попробовать упрощать запоос для потгресапока не станет ясно в чем проблема.

Можешь попробовать упрощать запоос для потгресапока не станет ясно в чем проблема.

ORDER BY b работает, любая операция с b ругается, что нет такого столбца. b можно выбирать из столбцов files без преобразований или с преобразованиями. То есть:
SELECT file AS b FROM files ORDER BY b; – работает.
SELECT file, status AS b FROM files ORDER BY b; – работает.
SELECT file AS b FROM files ORDER BY SUBSTR(b, 6); – ошибка.

емнип в постгресе order by работает только конкретно со столбцами, которые выбраны select-ом, выражения не прокатят.

емнип в постгресе order by работает только конкретно со столбцами, которые выбраны select-ом, выражения не прокатят.

Из-за чего?

И выражения работают. Но в качестве аргумента в них принимаются только столбцы. Алайасы нельзя.

Ну так-то, да, но если всё считать внутри ORDER BY, этот b и не нужен.

Тогда выкидывай)

Потому что в order by допустимы только select выражения.

классика, можно так

select a,b,c from table order by a + b

select a + b as sum, c from table order by sum + c

select a + b as sum, c, sum + c from table

select * from (select a + b as sum, c from table) order by sum + c

Для sqlite3 пока нашёл только советы, как писать расширение на Си.

Заведи отдельный столбец с ключами, приведенными в пригодный для сортировки вид. Тупо добить выравнивающими нулями:

scan/00000000008.txt
scan/00000000009.txt
scan/00000000079.txt
scan/00000000080.txt
scan/00000000088.txt
scan/00000000089.txt
scan/00000000090.txt
scan/00000000091.txt

REPLACE(SUBSTR(file, 6), ‘.txt’, '')

.txt необязательно удалять:

select * from files order by substr(file, 6)+0;

Через шелл и sort, например:

sqlite3 files.sqlite 'select * from files' -csv | sort -V

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

WITH RECURSIVE split_by_sym AS (
	SELECT 1 AS x, substr(file,1,1) AS s, f.file, f.status FROM files f
	UNION ALL
	SELECT sp.x+1, substr(sp.file, sp.x+1, 1) AS ss, sp.file, sp.status
	FROM split_by_sym sp
	WHERE ss != ''
)
SELECT
	file, status
	,CAST(group_concat(s,'') FILTER(WHERE s BETWEEN '0' AND '9') AS int) AS numbers_name
	,CAST(group_concat(s,'') AS text) AS real_name
FROM
	split_by_sym
GROUP BY
	file, status
ORDER BY
	numbers_name
;