SQL Sorting Queries

Anon

preliminary necessary information. Website runs php and mysql, many mysql tables, in this case the pertinent table is "books".

the pertinent parts of books are the columns title and subtitle.

I do queries to return lists of books sorted alphabetically (and by a number of other criteria).

the problem is that in a proper book catalog the title="The Dog Spot",subtitle="See How He Runs", is sorted by the letter "D" and not by the first letter of the string "The" (as articles such as 'A','An', 'The' are typically ignored in book catalogs, libaries etc)

So my question is, is there any kind of fancy SQL query that can do this without modifying the structure of the table?

I've got solutions to this problem, but all of them involve restructuring the database, and create more maintenance hassles.

any suggestions?

thanks!
Scott

Anon

Scott -

I don't know of a particularly elegant way of doing that offhand, but you could try something like this:

select title, subtitle, replace(replace(replace(title, "An ", ""), "A ", ""), "The ", "") as newField from books order by newField;

If you do that (appending any WHERE clauses you need, and tacking on any more REPLACE calls for other articles), you'll remove all A's, An's, and The's that are standalone words, and then alphabetize based on the resulting titles. The only problem I see is if those articles are capitalized in the middle of words (or if a word is all caps), in which case you could end up with a title going from PURITAN to PURIT. In those (hopefully rare) cases, though, the alphabetization should still be pretty accurate, if it changes at all.

Let me know how it goes.

-Jeremy

Anon

Scott -

On second glance, here's a better idea. Concatenate a space (" ") manually to the beginning of the title before you do the REPLACE, and then put a space before each article in the REPLACE call. The new query is:

select title, subtitle, replace(replace(replace(concat(" ", title), " An ", ""), " A ", ""), " The ", "") as newField from books order by newField;

That fixes a couple problems. My main concern was the very first word, which is the only standalone article that wouldn't have a space before it. Now that it's manually put there, you don't have to worry about mid-word replacements.

Now you need the REPLACE to be case-insensitive, to get the lower-case articles, too (which is the correct way for them to appear, right?). Just LCASE the title in the beginning to do that, and make all the articles lower-case as well.

FINALLY, you have:

select title, subtitle, replace(replace(replace(concat(" ", lcase(title)), " an ", ""), " a ", ""), " the ", "") as newField from books order by newField;

-Jeremy

Anon

Wow, that's great! I will be implementing this tomorrow and will let you know how it turns out in practice, but it looks really good to me.

one thought would be instead of inserting a space at the begging to insert some other character(s) that wouldn't ever appear, like "!@!" and then searching for that--that way nothing would be removed from the rest of the title.

looking at all the entries in the db, the first word of the title is always capitalized. but they just ignore a,an,the for indexing purposes...craziness 🙂

thanks so much, you've really helped me out!
Scott

kparker

Why is restructuring the database out of the question? While an on-the-fly solution has its benefits, one drawback is that it will always have to actually sort things, whereas with a static solution (e.g. separate column for the sortable version) in some cases the query may find the data already in the proper order based on an index and thus be able to skip the sorting step. It might also let you hand-tweak the few exceptions that might show up (e.g. A is for Apple probably shouldn't have the 'A' stripped.)