When I saw the title of this article, I immediately envisioned replacing:

...WHERE Name LIKE '%son'

with:

WHERE LEFT(REVERSE(Name),3)='nos'

Because to answer the introductory question, "How often do I need to do a search like...?": Not nearly often enough to justify creating a new indexed column solely for that purpose. A pure-SQL solution would be interesting though.