HQL sort by name ignoring optional 'The ' prefix

Question

So I have a named query which searches through records and sorts the outcome by a column named fullName. Some of these fullNames are prefixed by 'The ' - I'd like to amend the query to ignore these particular prefixes. Here's a look at a simplified version of the current named query, with a cut down list of conditionals:

select r from Record r where r.available = true ORDER BY r.fullName asc

Answer 1

Try this:

select r 
from Record r 
where r.available = true
ORDER BY 
    CASE 
        WHEN r.fullName LIKE "The%" THEN SUBSTRING(r.fullName, 5)
       ELSE r.fullName
    END asc