Hello,
Suppose that I have the following MySQL table:
CREATE TABLE addresses (
address varchar(255) NOT NULL default ''
) TYPE=MyISAM;
Add suppose that the content of this table are:
111 oak place #751
999 Birch Street Apt. 101
555 Oak Place #201
888 Birch Street #301
111 Oak Place #701
888 birch st
999 Birch Street No. 123
I need an SQL query that produces the following result:
Birch comes before Oak ->
Within Birch, 888 comes before 999 ->
within 999, Apt. 101 comes before No. 123, etc.
In other words, the sort is FIRST by street name (alphabetical,
ignoring case)... Also, when sorting, the SQL statement must treat
"Street" and "St." and "St" the same, and "Avenue" and "Ave." or
"Ave", and "Boulevard" and "Blvd." and "Blvd" the same.
Within the same street name (e.g. the four Birch streets), the sort is
by street number. And finally, within apartments on the same street
name & number, the sort is by apartment number.
*However*, when sorting by apartment number, the sort must ignore #,
Apt., apt., no., and any other prefixes before the actual number
itself.
The sorted result would look like this:
888 Birch Street #301
888 birch st
999 Birch Street Apt. 101
999 Birch Street No. 123
111 Oak Place #701
111 oak place #751
555 Oak Place #201
If this is impossible (or horrendously difficult in SQL), then I may
be able to relax one of the constraints... let me know which
constraints are the most difficult to satisfy.
Also, if I haven't offered enough $$ to solve this problem, please let
me know.
Since this query will be with a MySQL database, you may use any MySQL
functions that you need (I know that there are tons of String-analysis
functions built-in to MySQL). A PHP script will be executing the SQL,
e.g. mysql_query( "SELECT ..." );
Thanks,
Darren |
Request for Question Clarification by
hailstorm-ga
on
13 Feb 2003 20:55 PST
g8z,
Do you absolutely require this solution as a MySQL answer? Since you
mention that you are using PHP, it would seem to be a whole lot
simpler to just read all of the results in any old order, have PHP
split each one into the component parts of "house number", "street
name", and "apt. no" (when available), and then use PHP routines to
sort by "street name", "house number", and "apt. no".
|
Request for Question Clarification by
sycophant-ga
on
13 Feb 2003 23:53 PST
Would you be willing to restructure your table a little?
I would be more than happy to provide a suggestion for a new table
structure, computer with SELECT statements to achieve what you want.
Obviously with your existing content, it will require data
translation, but I should be able to supply a basic script to do that
as well, based on your specifications. Obviously if I can do this, I
could also solve the problem with PHP code, as mentioned by hailstorm,
but I think rearranging the table would be a better solution, over
all.
Let me know if your are insterested in pursuing that option.
Regards,
sycophant-ga
|
Clarification of Question by
g8z-ga
on
14 Feb 2003 00:39 PST
The reason that I want a solution that would fit entirely within the
MySQL query is that the sort of addresses will ultimately be
incorporated into a larger SQL statement that also contains sorts on
other fields. If PHP were used to do the sorting, then I'm concerned
that this would mess up the sorting of the other fields. I.e., the
address sort will most likely be the 3rd or 4th level sort in an
extremely large table.
Restructuring the table would not be possible, since I have a lot of
other code (100K+ lines across hundreds of PHP files) that relies on
the fact that the addresses are stored in a single field.
If providing an answer to possible to relax one or two constraints
rather than trying to do a PHP or table restructing solution.
These constraints can be relaxed if necessary:
- it would be acceptable to relax the Apt./No./# constraint. I.e., I
could require that data input of Apartment numbers precede with a #
only, so Apt. and No. will never be in the address.
- the St./Ave./Blvd. constraint could be relaxed. It would be possible
to restrict data entry a bit so that St. is always entered, but Street
never is. (i.e., abbreviations are always used)
These constraint relaxations may generate errors in the sort when
ultimately entered, but I'm guessing that the errors will be
infrequent and not extreme (i.e., the erroneous sort value will
probably still be very close to the correct sort position).
|
Clarification of Question by
g8z-ga
on
14 Feb 2003 09:23 PST
Would it instead be possible to have MySQL extract the first
token/word from the address, extract the last token from the address,
strip the last token of the # sign if present, and then sort according
to the three parts of the string? So after extracting the tokens, the
sort would be by middle token, first token, last token?
|
Clarification of Question by
g8z-ga
on
14 Feb 2003 09:38 PST
It seems to me that I could get the left-most token using a
combination of INSTR and LEFT, and INSTR and RIGHT to get the
right-most token, using the space ' ' as the substring in INSTR. Then,
the numeric part of the right-most token (the apartment number) could
be gotten again by using INSTR, with # as the substring, etc.
|
Clarification of Question by
g8z-ga
on
14 Feb 2003 10:50 PST
hi guys,
I've decided to close this question because I think that I can manage
something close to what I want using INSTR/LEFT/RIGHT MySQL functions.
Thanks for the feedback.
- Darren
|
First off, you want to use the sorting functionality built into MySQL.
It's foolish to do the sorting in PHP if you don't need to.
Returning result setse in sorted order based on multiple columns is a
core capability of the MySQL RDBMS and almost all RDBMSs (like Oracle,
Sybase, etc). But in order to use this functionality, you need to
break the data into separate columns - house number, street name, and
street number - as others have suggested above. To avoid modifying
your 100K+ lines of PHP, keep the column you've already got - the
address in one VARCHAR - and append these three new columns to the
table.
Wherever you save the data into the table, you can add logic to also
break the data up and store it in the three new columns. You can also
use similar logic to deal with your existing data.
One other thing: in the street name column, you'll need to normalize
all the abbreviations and capitalizations of "street" or "avenue" etc,
so that all entries use one form - "Street" or "Avenue" but never
"St." or "ave".
So a row in your revised table might look like this:
<table>
<tr><td>address</td><td>house number</td><td>street
name</td><td>apartment number</td></tr>
<tr><td>123 Oak St. #99</td><td>123</td><td>Oak
Street</td><td>99</td></tr>
</table>
Then you use the sorting capabilities that are already in MySQL.
Since you have broken your data into separate columns, you can sort by
each of the three new columns, and order the sorting as you see fit.
By this method you ought to be able to get the sorting you want, at
maximum speed, without modifying all your code. It does mean you have
to "denormalize" your data - which in this case means you'll have
several columns in the table that contain essentially the same data -
but that's OK; that's what you do sometimes. You'll also have a
bigger table which takes up more space, but I doubt that will be a
problem for you.
Regards,
larrye-ga |
