Postgresql sorting mixed alphanumeric data
You can simply cast name
column to bytea
data type allowing collate-agnostic ordering:
SELECT name
FROM folders
ORDER BY name::bytea;
Result:
name
--------------
a test
alphanumeric
test 1
test 10
test 19
test 20
(6 rows)
All of this methods sorted my selection in alphabetical order:
test 1
test 10
test 2
test 20
This solution worked for me (lc_collate: 'ru_RU.UTF8'):
SELECT name
FROM folders
ORDER BY SUBSTRING(name FROM '([0-9]+)')::BIGINT ASC, name;
test 1
test 2
test 10
test 20