CODEWITHSUNDEEP
mysql
Joe Hoskinson
Joe Hoskinson

Reputation: 217

MySQL Sort Alphabetically but Ignore "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:

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?

Upvotes: 10

Views: 5872

Answers (6)

Andrew
Andrew

Reputation: 8563

Simply:

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

Explanation:

We use the IF function to strip the "The" (if present) from the beginning of the string before returning the string to the ORDER BY clause. For more complex alphabetization rules we could create a user-defined function and place that in the ORDER BY clause instead. Then you would have ...ORDER BY MyFunction(Title).

Upvotes: 0

Luke Madhanga
Luke Madhanga

Reputation: 7467

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

Upvotes: 2

tony gil
tony gil

Reputation: 9564

if you are sure that you will NEVER EVER have a typo (and use lowercase instead of uppercase)

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

Otherwise your sorting will do all Upper and then all lower.

for example, this is ascending 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's answer

Upvotes: 4

Daniel Gimenez
Daniel Gimenez

Reputation: 20589

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 ;

Upvotes: 8

mdprotacio
mdprotacio

Reputation: 842

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;

Upvotes: 9

AJP
AJP

Reputation: 2125

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

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

Upvotes: 1

Related Questions