SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using PATINDEX VS. LIKE


Using PATINDEX VS. LIKE

Author
Message
womalley
womalley
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 3

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





Adam Machanic
Adam Machanic
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6887 Visits: 735
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
womalley
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 3

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
Adam Machanic
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6887 Visits: 735
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
womalley
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 3

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
Adam Machanic
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6887 Visits: 735
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
womalley
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 3

So could someone please answer my question?

How do PATINDEX and LIKE differ?


Thanks

Will





Adam Machanic
Adam Machanic
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6887 Visits: 735
PATINDEX is a function, whereas LIKE is a predicate.

--
Adam Machanic
whoisactive
womalley
womalley
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 3

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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search