Есть список файлов в таблице в PostgreSQL или SQLite. Команда select file,status from files order by file;
выводит в таком порядке:
file | status
--------------+--------
scan/79.txt | 0
scan/8.txt | 0
scan/80.txt | 0
scan/88.txt | 0
scan/89.txt | 0
scan/9.txt | 0
scan/90.txt | 0
scan/91.txt | 0
А мне надо сортировать числа в середине строк по возрастанию: 8-9-79-80-… Можно ли это сделать средствами SQL? Как такая сортировка называется?
Ответ: «natural sort order» – «естественная». Для Postgre нашлось готовое решение:
CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
SELECT * FROM files ORDER BY file COLLATE numeric;
Для sqlite3 пока нашёл только советы, как писать расширение на Си.
Или можно вычленить числа, преобразовать в числовой формат и сортировать по ним:
SELECT * FROM files ORDER BY CAST(REPLACE(SUBSTR(file, 6), '.txt', '') AS int);