sort order of address

[webgod]

Hi,

I have created a way to sort the order of my columns for a rental database. I am alowing links to sort by address, city, & state. However, the address field, as you might imagine, is a difficult sort.

Here are two address:
12 N. Buter St.
24 Adams Ct.

If I would sort this, Butler street would come before Adams Ct., due to the number is sorted in the begining, not the address. So, how do I script it to ignore the numbers, and compass (ie. N., W., E. S)?

[adamgeorge]

Why is the 'compass' mixed up in the stree address? Or is that how you write addresses where you are from?

Usually we just write house number streetname ie. 12 Grey St.

hmmm. anyway... what you could do is strip out all numerical characters with a regular expression, and then order the remaing data...

but I don't know how to do that in SQL syntax... I guess if you know anything about PL/SQL you could write a Stored Procedure to do that though...

... or you could just get the data from the database and do the strip characters and sort in PHP...

whichever way...

But I don't know that it will give you the results you are after... might be a bit hard to strip out the 'compas' bit... Maybe you could store the 'compas' as a seperate field in the database? Although if it's part of the street name, it might not be all that good of an idea...

anyway, goodluck!

-Adam 🙂

[xblue]

I'd strongly recommend to use 2 column then.
It may be possible to figure something out that does is with the one column, but I doubt that this could ever be efficient.