LIKE vs. =
June 29, 2009
Posted by on
There’s been quite a lot of changes in Ølen lately, but I’ll write more about that in a couple of days. Today I’ll tell you a couple of things about LIKE vs. = (equals). I got a phone call from Sigmund (btw, he’s much better now 🙂 today where he asked me why the following queries gave two different results:
SELECT something WHERE something = ‘string’
SELECT something WHERE something LIKE ‘string’
The first returned two records, while the second returned one. Strange? My first thought was unicode, but I didn’t actually know what happened here. The first record was ‘string’, while the second was ‘string ‘ (with a trailing space).
After some reading I found out that = removes trailing spaces. Just try for yourselves:
SELECT 1 WHERE CAST('a' AS VARCHAR(10)) = CAST('a ' AS VARCHAR(10))
SELECT 1 WHERE CAST('a' AS VARCHAR(10)) LIKE CAST('a ' AS VARCHAR(10))
Note that here we’re comparing ‘a’ to ‘a ‘ (five trailing spaces)
SELECT 1 WHERE CAST('a ' AS VARCHAR(10)) = CAST('a' AS VARCHAR(10))
SELECT 1 WHERE CAST('a ' AS VARCHAR(10)) LIKE CAST('a' AS VARCHAR(10))
Now we’ve switched places. We’re comparing ‘a ‘ to ‘a’. Suddenly it removes the trailing spaces on the left for both = and LIKE. Try changing to NVARCHAR instead of VARCHAR.
The conclusion is that when comparing strings it will always remove trailing spaces the left side, but only when using = on the right side.
EXCEPT if you’re using unicode 😀 When using LIKE on unicode it will not remove trailing spaces on either side! Confusing? Welcome to my world 🙂