Postgresql sorting mixed alphanumeric data
You can simply cast name column to bytea data type allowing collate-agnostic ordering:
SELECT nameFROM foldersORDER 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 1test 10test 2test 20This solution worked for me (lc_collate: 'ru_RU.UTF8'):
SELECT nameFROM foldersORDER BY SUBSTRING(name FROM '([0-9]+)')::BIGINT ASC, name;test 1test 2test 10test 20
select * from "public"."directory" where "directoryId" = 17888 order byCOALESCE(SUBSTRING("name" FROM '^(\d+)')::INTEGER, 99999999),SUBSTRING("name" FROM '[a-zA-z_-]+'),COALESCE(SUBSTRING("name" FROM '(\d+)$')::INTEGER, 0),"name";NOTE: Escape the regex as you need, in some languages, you will have to add one more "\".
In my Postgres DB, name column contains following, when I use simple order by name query:
- 1
- 10
- 2
- 21
- A
- A1
- A11
- A5
- B
- B2
- B22
- B3
- M 1
- M 11
- M 2
Result of Query, After I have modified it:
- 1
- 2
- 10
- 21
- A
- A1
- A5
- A11
- B
- B2
- B3
- B22
- M 1
- M 2
- M 11