Blog ala Vidar

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 🙂