Exclude "The", "An" & "A" from MYSQL Ordered list

Published: 12 years ago

The aim was to display results from my database in alphabetical order and not having words such "an" or "the" to impact on the alphabetical order (similar to iTunes album order).

So for example

The Doors would be displayed in the "D" artists

A Solution (Slow one)

One way of doing this I thought we could trim the appending name of the artist if it matched any preset criteria. This solution is a temporary one until I find a quicker method!

SELECT artist_name
FROM artists
ORDER BY TRIM( 
LEADING "A "
FROM TRIM( 
LEADING "An "
FROM TRIM( 
LEADING "The "
FROM LOWER( artist_name ) ) ) ) 
LIMIT 30

A Problem

When getting the results, the query does seem to take a little while

100 rows in set (0.29 sec)