MySQL Sort Alphabetically but Ignore "The"

Question

I have MySQL database that has a table with book data in it. One of the columns in the table is called "title". Some of the titles begin the word "the" and some do not.

Example:

• "The Book Title One"
• "Book Title Two"
• "Book Title Three"

I need to pull these out of the database in alphabetical order, but I need to ignore the "the" in the beginning of the titles that start with it.

Does SQL (specifically MySQL) provide a way to do this in the query?

Answer 1

You can use a CASE statement in the ORDER BY and the use REGEXP or LIKE to match strings that start with words you would like to remove.

In the example below I find all words that begin with a, an, or the followed by a space, and then remove everything up to the space, and trim away additional white space (you might have two or spaces following an instance of the).

SELECT *
FROM books
ORDER BY 
  CASE 
    WHEN title REGEXP '^(A|An|The)[[:space:]]' = 1 THEN 
      TRIM(SUBSTR(title , INSTR(title ,' '))) 
    ELSE title
  END ;

Answer 2

I've seen some convoluted answers here which I tried but were just wrong (didn't work) or unsafe (replaced every occurrence of 'the'). The solution I believe to be easy, or maybe I'm getting it wrong or not considering edge cases (sincerely, no sarcasm intended).

... ORDER BY SUBSTRING(UPPER(fieldname), IF(fieldname LIKE 'The %', 5, 1))

As stated elsewhere, UPPER just prevents ASCII sorting which orders B before a (note the case difference).

There's no need for a switch-case statement when there is only one condition, IF() will do

I'm using MySQL 5.6 and it seems like the string functions are 1-indexed, in contrast to say PHP where strings are 0-indexed (this caught me out). I've tested the above on my dataset and it works