Skip to main content
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Amsterdam) with: Ray Camden and David Huselid and Alison Huselid
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Amsterdam) with: Ray Camden ( @cfjedimaster ) David Huselid Alison Huselid

SQL LIKE Directive Faster Than LEN() Method

By
Published in

I was doing some SQL testing with my co-worker, Simon Free, about the speed differences between the SQL function LEN() and the SQL directive LIKE. Take for example the situation where I only want to get users who have a last name. In this case, I don't care the length of the last name, I only care if there is any value. To accomplish this, I used to do:

SELECT
	u.id
FROM
	[user] u
WHERE
	LEN( u.last_name ) > 0

This always worked. Fine. But it turns out this is slow compared to this:

SELECT
	u.id
FROM
	[user] u
WHERE
	u.last_name LIKE '_%'

This is using the wild card "_" to test the name as containing at least one character. This is sufficient for me. Not, now only was this consistently faster that LEN() (usually by half) but it is very consistent in speed. The Len() method would jump all over the place in speed. The LIKE method would always be the same (give or take a few ms).

Thinking about it, it makes sense. It's somewhat similar to the idea behind short-circuit evaluation. With LEN(), the server needs to evaluate EVERY character in a string. With the LIKE method, the server needs to evaluate one character; the second it hits a character, done, true, next record.

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

Reader Comments

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