Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2009 (Minneapolis, MN) with: Josh Knutson

Are SQL PATINDEX() And The SQL LIKE Clause Doing The Same Thing?

By Ben Nadel on
Tags: SQL

I know that running a loop and testing execution times is NOT a valid way of doing speed tests (but until I get not-lazy enough to learn how to properly load test, this is all I got). I think even doing this is valid in its own way, and if nothing else, at least opens the door for conversation. That being said, I recently found out about the SQL PATINDEX() function from Nathan Mische and Sana. Nathan was talking about finding a string of characters in a database column value and was using SQL's PATINDEX() to find this. I had dropped a comment about using the SQL LIKE construct to do the same thing (although my comment was in response to a total misunderstanding of how the PATINDEX() function worked).

Sana, then commented that PATINDEX() was much more powerful than LIKE. To me, though, they seem to be doing the same thing. And, with my primitive caveman speed test, they seem to operate at the same efficiency. In the following code, both the SQL PATINDEX() and the SQL LIKE clause operate consistently at the same speed:

  • <!---
  • Query for all the blog entries that have the phrase
  • "SEXY" in it. Do so using the SQL LIKE clause.
  • --->
  • <cftimer
  • type="outline"
  • label="SELECT Using LIKE Clause">
  •  
  • <!--- Loop over this method 10 times. --->
  • <cfloop index="i" from="1" to="10" step="1">
  •  
  • <!--- Query for blog information. --->
  • <cfquery name="qBlog" datasource="#REQUEST.DSN.Source#" result="objResult">
  • SELECT
  • b.id,
  • b.name
  • FROM
  • blog_entry b
  • WHERE
  • b.content LIKE '%[Ss][Ee][Xx][Yy]%'
  • </cfquery>
  •  
  • <!--- Output execution time of query. --->
  • #objResult.ExecutionTime# ms /
  • #qBlog.RecordCount# records
  • <br />
  •  
  • </cfloop>
  •  
  • </cftimer>
  •  
  •  
  • <!---
  • Query for all the blog entries that have the phrase
  • "SEXY" in it. Do so using the SQL PATINDEX() method.
  • --->
  • <cftimer
  • type="outline"
  • label="SELECT Using PATINDEX() Function">
  •  
  • <!--- Loop over this method 10 times. --->
  • <cfloop index="i" from="1" to="10" step="1">
  •  
  • <!--- Query for blog information. --->
  • <cfquery name="qBlog" datasource="#REQUEST.DSN.Source#" result="objResult">
  • SELECT
  • b.id,
  • b.name
  • FROM
  • blog_entry b
  • WHERE
  • PATINDEX( '%[Ss][Ee][Xx][Yy]%', b.content ) > 0
  • </cfquery>
  •  
  • <!--- Output execution time of query. --->
  • #objResult.ExecutionTime# ms /
  • #qBlog.RecordCount# records
  • <br />
  •  
  • </cfloop>
  •  
  • </cftimer>

In my example, I am using the pattern:

%[Ss][Ee][Xx][Yy]%

I was purposefully trying to make the pattern more complex in hopes that a bigger difference would be seen between the two algorithm choices. However, since my Database does not perform LIKE with case sensitivity, using this pattern:

%sexy%

... would have sufficed for both tests (but executed in about half the time as the previously defined pattern).

Now, I know that obviously they are doing different things: LIKE is a comparison operator and PATINDEX() returns the character index of the matched patterns - two different tasks. But, in terms of performance, if you do not care about the way in which it functions, they seem to operate at the same speed.

If all I care about is the comparison operator aspects used to filter a WHERE clause, is there any reason that I should be using PATINDEX() over the SQL LIKE operator?




Reader Comments

The biggest reason for using or not using PATINDEX probably would be whether or not you need the location of the value...maybe you might want to parse the value for some reason, like doing a replace on Sexy with another piece of text, or extract the value to use elsewhere.

PATINDEX is kind of like a combination of CHARINDEX and LIKE.

Reply to this Comment

@Tom,

That sounds about right to me. I was hoping maybe there was an actual speed increase just from switching to PATINDEX() over LIKE, but I perhaps that is just not the case.

Reply to this Comment

In my blatently unscientific tests PATINDEX was slower than LIKE. I searched 176546 Descriptions for matches to eight strings:

SELECT Count(*) FROM GSA_Work WHERE PATINDEX('%Storage%', Description) > 0 OR PATINDEX('%Storag%', Description) > 0
OR PATINDEX('%Storwks%', Description) > 0 OR PATINDEX('%Stor wks%', Description) > 0
OR PATINDEX('%Storwrks%', Description) > 0 OR PATINDEX('%Stor wrks%', Description) > 0
OR PATINDEX('%Stor rmkt%', Description) > 0 OR PATINDEX('%sureStor%', Description) > 0

5357 results that averaged around 2.2 seconds

SELECT Count(*) FROM GSA_Work WHERE [Description] LIKE '%Storage%' OR [Description] LIKE '%Storag%'
OR [Description] LIKE '%Storwks%' OR [Description] LIKE '%Stor wks%'
OR [Description] LIKE '%Storwrks%' OR [Description] LIKE '%Stor wrks%'
OR [Description] LIKE '%Stor rmkt%' OR [Description] LIKE '%sureStor%'

5357 results that averaged around 1.1 seconds

Reply to this Comment

@Kees,

I appreciate you posting these findings. I haven't been on SQL Server in a while (work mostly on MySQL these days), so I pretty much use LIKE for all my non-exact matching. Actually, I wonder if MySQL has something like this... I am shockingly poor at the intricacies of MySQL.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.