How will that work when people reside at 123A Some St.
Address that need to be sorted and/or grouped in any way should be stored as multiple fields.
door number door number suffix Most often a letter street name prefix Section street name street name suffix Direcetion street type St, Cr, Rd etc subdivision type Unit, Apt, Office etc subdivision Alphanumeric City State Postal Code
Reformating street address for address correction and for the purpose of distribution and/or statistics is a pain.
Try these:
105-1234 N 13th St E NY 1234 N 13th E St apt 105 1234 North 13th St East apt 105 New-York #105 1234 N Thirteenth St E NY
You get my drift... and I did not try appartment letter.
JLL
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.
Richard
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
