LEFT vs CHARINDEX for matching the start of a string in T-SQL
The biggest factor that I can think of is that LEFT(MyField,5) and CHARINDEX('HELLO',@MyField) won't use an index unless the index matches the expression exactly.  

However MyField LIKE 'HELLO%' will use any index with MyField in it.