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

Wow guys that was quick, thanks a lot for all answers, I'll try out your suggestions and get back to you soon. — emkay, Aug 24 '11 at 13:30

cdhowie · Accepted Answer · 2011-08-24T14:00:13.703

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.

Thanks for introducing me to views! I had no idea about them before. Would this be the correct syntax to create this particular view? `CREATE VIEW [Universities] AS SELECT SUBSTRING(name, 1, 14) = 'University of ', SUBSTRING(name, 15), name FROM list_universities` — emkay, Aug 24 '11 at 13:46
You need to omit the `[` and `]` characters from the name. Otherwise, yes -- but note that this will create a view with one column only. See my updated answer for a CREATE VIEW statement. — cdhowie, Aug 24 '11 at 13:59

score 2 · Answer 2 · answered Aug 24 '11 at 13:24

2

You can try

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

answered Aug 24 '11 at 13:24

Bala R

107,317
23
199
210

score 1 · Answer 3 · answered Aug 24 '11 at 13:26

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;

score 0 · Answer 4 · answered Aug 24 '11 at 13:28

0

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

answered Aug 24 '11 at 13:28

Rikon

2,688
3
22
32

Advanced MySQL Alphabetical Sort with Prefix?

4 Answers4

Linked