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

--
Sent via pgeu-general mailing list (pgeu-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgeu-general

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.albe@wien.gv.at]
Sent: Wednesday, March 20, 2013 1:43 PM
To: Umashanker, Srividhya; pgeu-general@postgresql.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

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'?

Yours,
Laurenz Albe

--
Sent via pgeu-general mailing list (pgeu-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgeu-general

(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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general