Thursday, February 16, 2012

charindex faster than like operator?

Hi ppl,
While I was working on the post "compare 2 strings" in my test db,
I came across this.
I ran these queries, the ones using the charindex and patindex were faster
than the like operator. I ran it in different tables with different inputs.
But same result.
I know server configs can affect the processing. But can some one run it in
their table and let me know. Plz. I am wordering whats the difference. Any
light on this?
Eager to know. Thanks a lot.
select top 100 * from tbl1 where namecol like '%food%'
select top 100 * from tbl1 where charindex('food',namecol) > 0
select top 100 * from tbl1 where patindex('%food%',namecol) > 0
select top 100 * from tbl1 where 'ab,cd' like '%' + namecol + '%'
select top 100 * from tbl1 where charindex(namecol,'ab,cd') > 0
select top 100 * from tbl1 where patindex('%' + namecol + '%','ab,cd') > 0use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')

No comments:

Post a Comment