The ideal way would be to normalize your design and split the two components of the column into two separate columns. One of type integer
, one text
.
With the current table, you could:
SELECT col
FROM tbl
ORDER BY (substring(col, '^[0-9]+'))::int -- cast to integer
, substring(col, '[^0-9_].*$'); -- works as text
The same substring()
expressions can be used to split the column.
These 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 to integer
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 an unambiguous separator, split_part()
is faster:
SELECT col
FROM tbl
ORDER BY split_part(col, '_', 1)::int
, split_part(col, '_', 2);
db<>fiddle here
See:
- Split comma separated column data into additional columns