Advanced MySQL Alphabetical Sort with Prefix?

Question

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 worded?)

Is it possible to do an alphabetical sort_by with a set prefix? For example I have a list of universities in a table. Some universities are prefixed by University of (e.g. University of Cambridge), while others are not (e.g. Durham University). Is it possible to define a prefix for MySQL to ignore?

As an example, the following list

University of Cambridge
University of Bristol
Durham University
kings College London

should be ordered to

University of Bristol
University of Cambridge
Durham University
Kings College London

Answer 1

You could do this:

ORDER BY 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:

CREATE VIEW Universities AS
    SELECT
        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.

Answer 2

You can try

ORDER BY REPLACE(LOWER(fieldName), 'university of', '')