One of the biggest differences is in application to the text fields. The charindex is not working (only for first 8000) while the patindex is. It returns the position while the like operation doesn't.