Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Have you looked into using full text search? –  Martin Smith Jul 29 '13 at 11:11
    
@MartinSmith full text search available in express edition? –  IT researcher Jul 29 '13 at 11:16
1  
Yes. You need to download sql server express with advanced services to get it though. –  Martin Smith Jul 29 '13 at 11:18

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.