MySQL – ORDER BY multiple words, then alphabetically?

How can I change this part of the query to have multiple words ordered first in sequence, then the rest of the results alphabetically?

ORDER BY CASE WHEN name LIKE '%Professional%' THEN 0 ELSE 1 END asc, name asc

So, it needs to be:

'%Professional%' 1
'%Leader%' 2
'%Advocate%' 3
'%Clinician%' 4
'%Educator%' 5
'%Scholar%' 6

Then all other results alphabetically.

Answer

You can just expand your CASE expression with each of the desired words:

ORDER BY 
CASE WHEN name LIKE '%Professional%' THEN 1
     WHEN name LIKE '%Leader%' THEN 2
     WHEN name LIKE '%Advocate%' THEN 3
     WHEN name LIKE '%Clinician%' THEN 4
     WHEN name LIKE '%Educator%' THEN 5
     WHEN name LIKE '%Scholar%' THEN 6
     ELSE 7
END,
name