Skip to main content
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Karl Swedberg
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Karl Swedberg ( @kswedberg )

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

By on
Tags:

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?

Want to use code from this post? Check out the license.

Reader Comments

5 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.

15,643 Comments

@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.

1 Comments

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

15,643 Comments

@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.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel