Having come across this, as a follow up if anyone's interested in benchmark comparisons, here's an easy to read posting:
http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex
which shows the speed differences between LEFT, RIGHT, LIKE, SUBSTRING, and CHARINDEX.