Fork me on GitHub

mysql

Correcting "The" Sorting Problem in MySQL

Names that contain "The ", like "The Eagles", don't sort in a useful way. You can use MySQL string functions to rememdy this. Below is a quick trick to sort a table by a column containing names, ignoring names that begin with "The ".

SELECT
  IF (
    LEFT(my_name_column, 4) = 'the ',
    CONCAT(RIGHT(my_name_column, LENGTH(my_name_column) - 4), ', The'),
    my_name_column
  )
  AS my_sortable_name_column
FROM my_table 
ORDER BY my_sortable_name_column ASC
Syndicate content