SQL ORDER BY with ‘The’ removed

I’ve often had to sort strings into alpha order but had problems with sorting the ones starting with “The”. Here’s how I solved it within the SQL query.

SELECT     partnerID,
           partner,
           partnersort =
                CASE LEFT(partner,4)
                WHEN 'the' THEN RIGHT(partner,LEN(partner)-4)
                ELSE partner
                END
FROM       partners
ORDER BY   partnersort

Pretty happy with that!

Advertisement