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

Re: Sorting street addresses

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Richard Poole <rp(at)guests(dot)deus(dot)net>
Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Sorting street addresses
Date: 2004-10-28 21:55:41
Message-ID: 41816ADD.9030002@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-general
Richard Poole wrote:
> 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.

You could also add a function index that would help speed things along.

Sincerely,

Joshua D. Drake



> 
> Richard
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)


-- 
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachment: jd.vcf
Description: text/x-vcard (640 bytes)

In response to

pgsql-general by date

Next:From: Jim C. NasbyDate: 2004-10-28 22:37:29
Subject: Turning a subselect into an array
Previous:From: Richard PooleDate: 2004-10-28 21:31:24
Subject: Re: Sorting street addresses

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