Re: Alphanumeric natural order sorting

We are working on a framework, where the client can

* call for sort on any colmn.

* The digits may or may not be there

* The numeric can be anywhere in the string

-vidhya

-----Original Message-----
From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
Sent: Wednesday, March 20, 2013 1:43 PM
To: Umashanker, Srividhya; pgeu-general(at)postgresql(dot)org
Subject: 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