MySQL Sort alphabetically, but ignore "The"

Question

I have a MySQL database in which there is a table with the data of the book. One of the columns in the table is called "title". Some of the names begin the word "the", and some do not.

Example:

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

I need to get them out of the database in alphabetical order, but I need to ignore "the" at the beginning of the headers that start with it.

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

Answer 1

follow the case where you need to check whether the column value starts with and, if so, return the header without "The". This will be a new column, which you will use later for sorting order.

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

Answer 2

You can use the CASE statement in ORDER BY and use REGEXP or LIKE to match lines starting with the words you would like to delete.

In the example below, I find all words starting with a , or and then a space, and then remove everything to the place and remove the extra space (you may have two or spaces after the instance )

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

Answer 3

if you are sure you will never have a typo (and use lowercase letters instead of uppercase)

 select * from books b order by UPPER(LTRIM(Replace(b.Title, 'The', ''))) 

Otherwise, your sort will do all the top ones, and then everything below.

for example, this is an increasing order:

 Have a Great Day Wild west Zorro aZtec fries are hotter alfred goes shopping bart is small will i am not 

adapted from AJP answer

Answer 4

 select * from books b order by LTRIM(Replace(b.Title, 'The', '')) 

PLease note that this will replace The from the title .. no matter where in the title. so use a substring to get the first 3 characters.

Answer 5

I saw some confusing answers here that I tried, but were simply erroneous (did not work) or unsafe (replaced each occurrence of "the"). A decision that I think is easy, or maybe I'm wrong or don't consider extreme cases (sincerely, without sarcasm).

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

As indicated elsewhere, UPPER simply prevents ASCII sorting, which orders B to a (note the difference in case).

There is no need for a switch-case , when there is only one condition, IF() will execute

I am using MySQL 5.6 and it seems that the string functions are 1-indexed, unlike PHP, where the rows are indexed 0 (this caught me). I tested above on my dataset and it works

Answer 6

Just:

 SELECT Title FROM book ORDER BY IF(Title LIKE "The %", substr(Title, 5), Title); 

Explanation:

We use the IF function to remove "The" (if present) from the beginning of the line before returning the line to the ORDER BY . For more complex alphabet rules, we could create a user-defined function and put it in the ORDER BY . Then you will have ...ORDER BY MyFunction(Title) .