Alphanumeric sorting with PostgreSQL
The ideal way would be to normalize your data and split the two components of the column into two individual columns. One of type integer
, one text
.
With the current table, you can do something like demonstrated here:
WITH x(t) AS ( VALUES ('10_asdaasda') ,('100_inkskabsjd') ,('11_kancaascjas') ,('45_aksndsialcn') ,('22_dsdaskjca') ,('100_skdnascbka') )SELECT tFROM xORDER BY (substring(t, '^[0-9]+'))::int -- cast to integer ,substring(t, '[^0-9_].*$') -- works as text
The same substring()
expressions can be used to split the column.
The regular expressions are somewhat fault tolerant:
The first regex picks the longest numeric string from the left,
NULL
if no digits are found, so the cast tointeger
can't go wrong.The second regex picks the rest of the string from the first character that is not a digit or '_'.
If the underscore is unambiguous as separator anyway, split_part()
is faster:
ORDER BY (split_part(t, '_', 1)::int ,split_part(t, '_', 2)
Answer for your example
SELECT nameFROM nametableORDER BY (split_part(name, '_', 1)::int ,split_part(name, '_', 2)
You can use regular expressions with substrings
order by substring(column, '^[0-9]+')::int, substring(column, '[^0-9]*$')
There is a way to do it with an index over an expression. It wouldn't be my preferred solution (I would go for Brad's) but you can create an index on the following expression (there are more ways to do it):
CREATE INDEX idx_name ON table (CAST(SPLIT_PART(columname, '_', 1) AS integer));
Then you can search and order by CAST(SPLIT_PART(columname, '_', 1) AS integer)
every time you need the number before the underline character, such as:
SELECT * FROM table ORDER BY CAST(SPLIT_PART(columname, '_', 1) AS integer);
You can do the same to the string part by creating an index on SPLIT_PART(columname, '_', 2)
, and then sort accordingly as well.
As I said, however, I find this solution very ugly. I would definitely go with two other columns (one for the number and one for the string), then maybe even removing the column you mention here.