Re: Alphanumeric natural order sorting

Umashanker, Srividhya wrote:
> I am looking for a solution the Alphanumeric sorting
>
> I tried all possible collation example -- select * from test order by name collate "en_US";
>
> Is there someone who has solved this problem with writing a function?

> I am expecting
>
> 1, bay1
> 2, bay2
> 10, bay10
> 11, bay11

That is not the correct sort order in any collation,
because either '1' < '2' or '1' > '2' (assuming that '1' <> '2').

You can pick out the parts with a regular expression:
SELECT * FROM test
ORDER BY regexp_replace(name, '^([^[:digit:]]*).*$', '\1'),
regexp_replace(name, '^.*?([[:digit:]]*)$', '\1')::bigint;

Or you split the column into two columns, one a string and
the other a number, and use these for sorting.

Yours,
Laurenz Albe