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 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 L开发者_如何学Condon

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.

You can try

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

one of the possible way

order by replace(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
order by order_name;

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