If anyone was interested in speed comparisons, it seems CHARINDEX is a lot faster than LIKE and others. The benchmarks can be found here.

It's quite interesting because charindex seems to have wiped out everything else.