Re: Sorting street addresses

From: Richard Poole <rp(at)guests(dot)deus(dot)net>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Sorting street addresses
Date: 2004-10-28 21:31:24
Message-ID: 20041028213124.GC11905@guests.deus.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Oct 28, 2004 at 03:36:00PM -0400, Robert Fitzpatrick wrote:

> I would like all those on the same street grouped together. Is there any
> tricks to getting the street names sorted first, possibly where numbers
> and strings separate?

You could do something like

CREATE FUNCTION streetname(text) RETURNS text AS '
SELECT substring($1 FROM ''[a-zA-Z ]+$'')
' LANGUAGE 'SQL';

and then add an ORDER BY streetname(address) to your select.

Richard

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2004-10-28 21:55:41 Re: Sorting street addresses
Previous Message CSN 2004-10-28 21:08:21 Derived tables?