Advanced Mysql Alphabetical Sort With Prefix?

Apologies if this question has already been answered, I've already done an extensive search but haven't come across an an answer (probably because I'm not sure how it's properly wo

Solution 1:

You could do this:

ORDERBY IF(SUBSTRING(name, 1, 14) ='University of ', SUBSTRING(name, 15), name)

It might be a good idea to create a view over this table projecting an extra name_value column set to the IF() expression above. Then you can order by this column and select it without having to pollute your queries with IF().

---

Example view, assuming that the university name is stored in the column name:

CREATEVIEW Universities ASSELECT
        list_universities.*,
        IF(SUBSTRING(name, 1, 14) ='University of ',
           SUBSTRING(name, 15),
           name) AS name_value
    FROM list_universities;

Then you can select from Universities the same way you do from list_universities, except it will have an extra name_value column that you can select, or order by, or whatever.

Note that this approach (as well as ORDER BY IF(...)) won't be able to use any index on name to improve the performance of the sort.

Solution 2:

You can try

ORDERBY REPLACE(LOWER(fieldName), 'university of', '')

Solution 3:

one of the possible way

order byreplace(display_name, 'University of', '');

however, applying function to alter value of a column will resulted index neglected by mysql often people will consider to duplicate another column, and this column normally strip off those unwanted words (or arrange the value into the manner that sorting can work)

assuming the clean field is named as order_name, it should consists of

Bristol, University
Cambridge, University
Durham, University
Kings College Cambridge
Kings College London

so, the SQL could be

select display_name 
from tables
orderby order_name;

Solution 4:

I put this together in SQL Management Studio, so I assume this is valid MySql (I avoided using a 'with' for example)

SELECT UniversityName, REPLACE(UniversityName, 'University of', '') AS cleanName 
FROM Universities c
ORDERBY cleanName