X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.225.181])
	by svr1.postgresql.org (Postfix) with ESMTP id 4BC2DCC6D7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 16 Jul 2003 16:36:28 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new,
	port 10024) with ESMTP id 55984-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 16 Jul 2003 16:36:44 +0000 (GMT)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id C1CD9CC6C8
	for <pgsql-sql@postgresql.org>; Wed, 16 Jul 2003 13:36:16 -0300 (ADT)
Received: (qmail 4627 invoked by uid 500); 16 Jul 2003 16:41:06 -0000
Date: Wed, 16 Jul 2003 11:41:06 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: numerical sort on mixed alpha/numeric data
Message-ID: <20030716164106.GC1263@wolff.to>
Mail-Followup-To: Gary Stainburn <gary.stainburn@ringways.co.uk>,
	pgsql-sql@postgresql.org
References: <200307161248.26835.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200307161248.26835.gary.stainburn@ringways.co.uk>
User-Agent: Mutt/1.5.4i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/205
X-Sequence-Number: 14319

On Wed, Jul 16, 2003 at 12:48:26 +0100,
  Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> Hi folks,
> 
> I've got a table holding loco numbers and an id which references the locos 
> table. How can I sort this table, so that numeric values appear first in 
> numerical order followed by alpha in alpha order.?

You can first sort by whether or not a pattern match succeeds. You haven't
said whether or not the empty string is a number or an alpha and that will
affect the choice of pattern.

For example:
select * from lumbers order by lumber !~ '^[0-9]+$', lumber;