X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4A0DA528C9
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 May 2005 16:20:21 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 07813-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 May 2005 19:20:16 +0000 (GMT)
Received: from tht.net (vista.tht.net [216.126.88.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 952A7528F2
	for <pgsql-sql@postgresql.org>; Mon, 16 May 2005 16:20:15 -0300 (ADT)
Received: from 216-154-15-180.dsl.look.ca (216-154-15-180.dsl.look.ca
	[216.154.15.180]) by tht.net (Postfix) with ESMTP
	id C354976A14; Mon, 16 May 2005 15:20:19 -0400 (EDT)
Subject: Re: ORDER BY handling mixed integer and varchar values
From: Rod Taylor <pg@rbt.ca>
To: David B <davidb999@gmail.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <c76ea21c050516114742818cfa@mail.gmail.com>
References: <c76ea21c050516114742818cfa@mail.gmail.com>
Content-Type: text/plain
Date: Mon, 16 May 2005 15:19:24 -0400
Message-Id: <1116271164.696.80.camel@home>
Mime-Version: 1.0
X-Mailer: Evolution 2.2.2 FreeBSD GNOME Team Port 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200505/114
X-Sequence-Number: 21427

> Is there any elegent query you folks can think of that combines the
> two so I can one query that has alpha sorting on alpha categories and
> numeric sorting on numeric values that are in the same column??

select * from r order by (case when col ~ '^[0-9]+$' THEN lpad(col, 10,
'0') else col end) ;

Left pad the numbers with 0's, but don't touch the text strings. Sort
based on that.
--