Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using PATINDEX VS. LIKE Expand / Collapse
Author
Message
Posted Friday, November 5, 2004 11:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 21, 2008 9:22 AM
Points: 65, 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




Post #144992
Posted Friday, November 5, 2004 11:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 1,113, Visits: 707
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #144995
Posted Friday, November 5, 2004 12:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 21, 2008 9:22 AM
Points: 65, 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




Post #145005
Posted Friday, November 5, 2004 2:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 1,113, Visits: 707
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #145039
Posted Monday, November 8, 2004 5:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 21, 2008 9:22 AM
Points: 65, 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




Post #145152
Posted Monday, November 8, 2004 3:09 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 1,113, Visits: 707
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #145313
Posted Tuesday, November 9, 2004 3:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 21, 2008 9:22 AM
Points: 65, Visits: 3

So could someone please answer my question?

How do PATINDEX and LIKE differ?


Thanks

Will




Post #145525
Posted Tuesday, November 9, 2004 3:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 1,113, Visits: 707
PATINDEX is a function, whereas LIKE is a predicate.

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #145527
Posted Tuesday, November 9, 2004 3:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 21, 2008 9:22 AM
Points: 65, 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




Post #145531
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse