Using like over charindex to search for pattern

Question

We where using charindex to search for a string as shown below

SELECT *
FROM tablename
WHERE ( Charindex('47a%$.abc',CAST(columnname AS VARCHAR(MAX)))>0 )

I am planning to replace charindex with LIKE as i require to search only whether the pattern exists not the position. So i want to know is these any disadvantage in using LIKE over charindex for my case.I am using charindex in several cases. So i want it to work for all sql datatypes ,all characters including special characters(including % (as like already uses %pattern%).So please tell me is there any disadvantage in using like over charindex

Answer 1

The disadvantage is that you need to escape all characters of special significance in the pattern syntax.

Following your previous question it seems the way you do the escaping affects the cardinality estimates.

Testing on SQL Server 2008/2012

SELECT name
/*Copying into a temp table as on 2012 this table is now in the readonly 
 resource database*/
INTO #T
FROM   master..spt_values

Then

SELECT *
FROM   #T
WHERE  name LIKE '%\%%' ESCAPE '\' 

gives an estimated row count of 625.33/627.575. But

SELECT *
FROM   #T
WHERE  name LIKE '%[%]%' 

gives an estimated row count of 1.8563/1.86296.

In fact no name values in that table contain a literal % character so the second one appears to be more accurate in this case.

The response to this Connect Item states

We do not have support for precise cardinality estimation in the presence of user defined escape characters. So we probably get a poor estimate and a poor plan. We'll consider addressing this issue in a future release.