MySQL - Ordering alphabetically, excluding "The"

Question

I'm looking for a way to query my database and alphabetize the results, but if the entry starts with "The" to skip over it and instead alphabetize along the first letter of the next word.

Is it possible to do this with an SQL query, or would I need to use some PHP to filter entries beginning with "The" and then re-assemble them?

I've searched for solutions to this problem, but it's tough finding results when the main keyword of the search is the literal "THE". Any help would be much appreciated!

score 6 · Accepted Answer · answered Nov 18 '13 at 22:26

6

You can do this with a SQL query, by using a conditional statement in the order by:

order by (case when keyword like 'The %' then substr(keyword, 5, 100)
               else keyword
          end)

answered Nov 18 '13 at 22:26

Gordon Linoff

1,242,037
58
646
786

score 3 · Answer 2 · answered Nov 18 '13 at 22:27

3

SELECT title, IF(title LIKE "The %", CONCAT(SUBSTR(title, 4), ", The"), title) AS reordered_title
FROM mytable
ORDER BY IF(title LIKE "The %", CONCAT(SUBSTR(title, 4), ", The"), title)

Here's a fiddle

answered Nov 18 '13 at 22:27

elixenide

44,308
16
74
100

Mike Brant · Answer 3 · 2013-11-18T22:38:10.903

Your best bet might be to have a title prefix field which contains entries like The, A, An etc. that you don't wish to consider for alphabetizing. Other suggestions noted here can work, however they would disallow the use of an index for sorting, which could be very problematic for your application if you have large number of rows. In this case the query might look like

SELECT CONCAT(title_prefix, title) AS `full_title`, ...
FROM table
ORDER BY title ASC

Modifying your existing table to accommodate this approach would be simple. Simply add the name column, then run a few updates like

UPDATE table SET title_prefix = 'The ', title = SUBSTR(title, 4) WHERE title LIKE 'The %';
UPDATE table SET title_prefix = 'A ', title = SUBSTR(title, 2) WHERE title LIKE 'A %';
UPDATE table SET title_prefix = 'An ', title = SUBSTR(title, 3) WHERE title LIKE 'An %';

Even if you don't want a separate field, you could use similar update logic to move the prefixes to the end of the title name like this

UPDATE table SET title = CONCAT(SUBSTR(title, 4), 'The, ') WHERE title LIKE 'The %';
etc.

Either way would allow you to sort by title while also utilizing an index.

score 1 · Answer 4 · answered Nov 18 '13 at 22:28

1

and one more solution:

select * from table
order by if(lower(substring(`field`, 1, 4)) = 'the ', substring(`field`, 5), `field`)

answered Nov 18 '13 at 22:28

Iłya Bursov

23,342
4
33
57

rubo77 · Answer 5 · 2013-11-18T22:49:03.917

Try using something like

SELECT * from tablename order by REPLACE(fieldname, 'The ', '');

which is not perfect, due to sentences with "the" in the middle, so if that could be the case you could replace other instances of "The" before:

SELECT * from tablename order by 
    REPLACE(
        REPLACE(
            REPLACE(fieldname, ' The', '&The&'),  
                'The ',''
        ),
        '&The&', ' The'
    );

see string functions for more ideas.

If you can update to MariaDB 10, you can use

 SELECT * from tablename order by REGEXP_REPLACE(fieldname,'^[Tt]he ','')

see: https://mariadb.com/kb/en/regexp_replace/

MySQL - Ordering alphabetically, excluding "The"

5 Answers5