mysql末字母排序,MySQL按字母顺序排序，但忽略“ The”

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?

解决方案

do a case when to check if the column value starts with the and if it does, return the title without the 'The'. This will be a new column that you will be using later on for the sort order

select title, case when title like 'The %' then trim(substr(title from 4)) else title end as title2 from tablename order by title2;