Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

Next:From: Joshua D. DrakeDate: 2004-10-28 21:55:41
Subject: Re: Sorting street addresses
Previous:From: CSNDate: 2004-10-28 21:08:21
Subject: Derived tables?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group