Re: [pgeu-general] Alphanumeric natural order sorting

(pgeu-general is not the right list for technical discussions, moving to
pgsql-general)

On 20.03.2013 10:46, Albe Laurenz wrote:
> Umashanker, Srividhya wrote:
>>> I am looking for a solution the Alphanumeric sorting
>
>>> I am expecting
>>> 1, bay1
>>> 2, bay2
>>> 10, bay10
>>> 11, bay11
>
>> 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
>
> That's easy then.
> Just define exactly how you want the ordering to be,
> and based on that definition you can write code for
> sorting.
>
> How would you sort
> 'bay10', 'ba1y0', 'ba10y', 'ba2y0'?

The OP is asking about "natural sort order". See
http://www.codinghorror.com/blog/2007/12/sorting-for-humans-natural-sort-order.html
for example.

There are a few projects out there for doing that in various programming
languages, but I'm not aware of anything for PostgreSQL. Maybe you could
pick one of the existing functions listed in that blog post, for
example, and write a PL function using them.

See also:
http://blog.ringerc.id.au/2012/10/natural-sorting-example-of-utility-of.html

- Heikki