CHARINDEX vs. LIKE | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive


Can you please advise which where clause will give better performance. WHERE CHARINDEX(‘cos’,product) <> 0 or WHERE product LIKE ‘%cos%’
Thanks in advance for your input.
I tried these in QA with estimated execution plans. I tried it with a field with a clustered index, a field with a non clustered index and an unindexed field on the same table, with over 1.5 million rows.
The results were minimally different, with the charindex having slightly (.01%) better estimated performance. Using patindex was only .01% better than charindex.