Using PATINDEX VS. LIKE

Question

Using PATINDEX VS. LIKE

womalley

Ten Centuries

Points: 1329
More actions
November 5, 2004 at 11:22 am

#161773

Hello,
I just noticed something very interesting and was hoping that someone could shead
some light on the subject.
I noticed that when I use PATINDEX in a query rather then LIKE
the query returns faster but, has a much larger sort cost.
Is PATINDEX better to use when searching for a string?
How do LIKE and PATINDEX differ?
Thank you for the help
William O'Malley

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

I've read in various places that PATINDEX was faster than LIKE, but have never been able to reproduce it on my end. Can you post some code that proves that it's faster, or at least show what kinds of patterns/data you're searching on, and I'll generate some test code to stress it? I'm very curious about why I keep seeing this and would like to get a definitive answer...

--
Adam Machanic
whoisactive

womalley Ten Centuries Points: 1329 More actions · Answer 2

Where I am using this is in a PartNumber search

So say I pass in part of a PartNumber

Here is an Example

Using LIKE

SELECT * FROM MyTable

Where LTRIM(RTRIM(PartNumber)) LIKE '%' + LTRIM(RTRIM(@PassedPartNumber))

---

Using PATINDEX

This returns the recordset faster then the above select

SELECT * FROM MyTable

Where PATINDEX('%'+LTRIM(RTRIM(@PassedPartNumber))+'%' , PartNumber) > 0

---

Will

Adam Machanic SSCoach Points: 15370 More actions · Answer 3

How big is your partnumber data?

Would you mind telling me:

A) The data type of the column

B) the output from:

SELECT AVG(LEN(PartNumber)) AS AvgLen,

MAX(Len(PartNumber)) AS MaxLen,

MIN(Len(PartNumber)) AS MinLen,

COUNT(*) AS Count

FROM YourTable

and C) Could you post some sample data along with what kind of values you get as your @PassedPartNumber ?

I would really appreciate it. As I said, I have never been able to get PATINDEX return faster, so I'm very curious about when and why it does in your case. Thanks!

--
Adam Machanic
whoisactive

womalley Ten Centuries Points: 1329 More actions · Answer 4

A) The column is a Varchar(50)

B) AvgLen = 10

MaxLen = 32

MinLen = 2

Count = 82325

C) Because of the industry that I work in, I can not provide

A) Sample Data

B) Table Layouts

C) ER Diagrams

However I can tell you that @PassedPartNumber would be part or all of a part number

So @PassedPartNumber could be '269' or '123' or '24d121' ... ect

Thank you again for your interest in this. Something else I found to be interesting..

No matter how large the dataset the results are the same, PATINDEX

works faster then LIKE in ALL of the selects I have tried.

Will

Adam Machanic SSCoach Points: 15370 More actions · Answer 5

FYI, I posted my own test results here, along with an invitation for someone to show me how to prove that PATINDEX will, indeed, outperform LIKE:

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/08/4993.aspx

--
Adam Machanic
whoisactive

womalley Ten Centuries Points: 1329 More actions · Answer 6

So could someone please answer my question?

How do PATINDEX and LIKE differ?

Thanks

Will

Adam Machanic SSCoach Points: 15370 More actions · Answer 7

PATINDEX is a function, whereas LIKE is a predicate.

--
Adam Machanic
whoisactive

womalley Ten Centuries Points: 1329 More actions · Answer 8

ok.. ok.. i asked for that one..

When looking at how the Query Engine works. There must be a difference in how PATINDEX and LIKE work. What I would like

to know is what magic goes on under the covers...

Thanks for your replys..

Will