Sorting Alphabetically but ignoring definite articles

my8by10

I'm not sure if this is something that can be done in a MySQL query or not, but is there a way to have items in a list sorted alphabetically, but ignore any definite articles (a, an, the) in the sort? I have a whole bunch of titles and I want them sorted properly, i.e. I don't want all of the "The" titles together.

For example, I've got three items in a table:

"A Rock"
"The Banana"
"Nose Clippers"
"An Elephant"

If I were to sort it alphabetically, it would show up as:

"A Rock"
"An Elephant"
"Nose Clippers"
"The Banana"

What I'm hoping for is a way to have it sort this way:

"The Banana"
"An Elephant"
"Nose Clippers"
"A Rock"

Any hints?

sfullman

First of all you really MUST index this column for best results.

Second of all, it might be good to have a second column with values like
Kill a Mockingbird, To (although that should stay the same :-)
Basket, The

It doesn't look like mysql has a function like str_replace. I suggest you get everything into an array, clear out the result, and do some work on teh array in php or whatever (and very quick at that when you use the functions right).

On the other hand you may have 1000s of titles and so it really needs to be done in teh query to get "LIMIT n, batch" without getting 1000s of unneeded rows in a local array.

The query in the mysql would be rather complex, here's the mysql manual link:
http://dev.mysql.com/doc/mysql/en/Function_Index.html

Look at replace() and also substring() and IF(condition, action1, action2), something like

SELECT
IF( LOCATE('The ', Title)=1, CONCAT(SUBSTRING(), ', The'),
IF( LOCATE('An ' , Title)=1, CONCAT(SUBSTRING(),', An'),
IF( LOCATE('A ' , Title)=1, CONCAT(SUBSTRING(),', A'),
IF( LOCATE('Of ' , Title)=1, CONCAT(SUBSTRING(),', Of'),
Title)))) AS IndexedTitle
FROM
movielist
ORDER BY IndexedTitle ASC
[LIMIT 100, 20]

I'll let you figure out how to use substring() :-)

Sam

drawmack

What I would do is have php fix it on insert so they were put into the database as Rock, The, then I could just sort them with mysql and have php put them back the right way, or even just leave them reordered as most people should be used to seeing that sort of thing anyway.

my8by10

Okay, based on Sam's advice, I did this:

Assuming m_title is the title and media is the table...

SELECT * , 
 IF ( 
LOCATE( 'The ', m_title ) = 1, CONCAT( SUBSTRING( m_title, 4 ) , ', The' ) , 
 IF ( 
LOCATE( 'An ', m_title ) = 1, CONCAT( SUBSTRING( m_title, 3 ) , ', An' ) , 
 IF ( 
LOCATE( 'A ', m_title ) = 1, CONCAT( SUBSTRING( m_title, 2 ) , ', A' ) , m_title 
) 
) 
) AS newtitle 
FROM media 
ORDER BY newtitle ASC

When I got the results back, the order was off. The affected rows were reported first, and then the unaffected rows were reported, i.e.:

"Banana, The"
"Elephant, An"
"Rock, A"
"Nose Clippers"

Even though Nose Clippers should have been before A Rock.

Any hints as to why this might be happening?

drawmack

Because it's ordering it based on the actual field in the database. See you should take my advice.

sfullman

That's wierd.

Try stating, instead of ORDER BY newtitle, ORDER BY IF(.........) (in other words, the long string).

I can't imagine that would give you a problem.

Sam

sfullman

I tested it and it is changing the order but the spaces affect the sort, this WILL WORK (note the REPLACE() which removes spaces):

SELECT 
IF ( LOCATE(  'The ', m_title )  =1, CONCAT( SUBSTRING( m_title, 4  ) ,  ', The'  ) , 
IF ( LOCATE(  'An ', m_title )  =1, CONCAT( SUBSTRING( m_title, 3  ) ,  ', An'  ) , 
IF ( LOCATE(  'A ', m_title )  =1, CONCAT( SUBSTRING( m_title, 2  ) ,  ', A'  ) , m_title ) ) ) AS abc
FROM media
ORDER  BY 
REPLACE(LCASE( 
IF ( LOCATE(  'The ', m_title )  =1, CONCAT( SUBSTRING( m_title, 4  ) ,  ', The'  ) , 
IF ( LOCATE(  'An ', m_title )  =1, CONCAT( SUBSTRING( m_title, 3  ) ,  ', An'  ) , 
IF ( LOCATE(  'A ', m_title )  =1, CONCAT( SUBSTRING( m_title, 2  ) ,  ', A'  ) , m_title )  ) ) )  ,' ','') ASC

Apparently mySQL has a differernt idea of a sort than what we're thinking, if you'll research that. This is just to show that it could be done, not necessarily that it's the best way.