Sort entry titles alphabetically excluding 'The'?

Question

I need to list the names of all of my entries in alphabetical order excluding 'the' (which could only be at the beginning). I've looked around online, but I was only able to find a code segment (which I've modified to fit my needs). However, it's still incorrectly sorting them as you can see in the attached image.

Current code:

{% for entry in craft.entries.section('partners').featuredMediaPartner('1').order('(CASE WHEN title LIKE "The %" THEN SUBSTRING(title, 3) ELSE title END ) asc') %}

Please help me fix this issue, thanks.

Answer 1

According to SUBSTRING, the first character starts at 1. So if you wanted to start at the position immediate after The (with one space), you'd need to start at position 5.

Just did a test locally and it works as expected for me if I use:

.order('(CASE WHEN title LIKE "The %" THEN SUBSTRING(title, 5) ELSE title END ) asc')