CHARINDEX vs LIKE ... in a WHERE clause

[JulianUK]

Hi all

I have a need to use either CHARINDEX or a LIKE pattern to find the occurrence of a single char in a field, within a WHERE clause.

e.g.
WHERE field1 like '%X%'
vs.
WHERE CHARINDEX('X', field1) > 0

Which would be more efficient? I've gone for the CHARINDEX so far.

Thanks

Julian

 

[nigelrivett]

I think patindex is meant to be fastest - or at least it was.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jaywalk]

I ran a couple of quick tests against a fully indexed table with 1.3 million rows, and it does appear that for your example charindex is quicker.

Like: 5 seconds (avg over 10 runs)
Charindex: 4 seconds (avg over 10 runs)

However, for searches on beginning of data, like outperformed patindex.

Like: almost instantly.
PatIndex: 1 second

These tests aren't exhaustive, so I'd recommend trying both in your specific application and seeing which is faster for you.

Bear in mind that when I removed the index for the column, both queries went up to approx 6 minutes each.

Jaywalk

 

[JulianUK]

Thanks to you both.

Nigel - I can't see that PATINDEX will be quicker than CHARINDEX, just the opposite I would've thought?

Jaywalk - many thanks for taking the time to do that. I was having trouble time testing it in my particular application as its part of a ridiculously convoluted query anyway. Its already slow enough too!

Luckilly my table wont be anywhere near 1.3 million rows!

Cheers

Julian