Terrifying SQL Server Behavior

So I run these queries:

1
2

SELECT count(id) FROM tbl_main WHERE some_field = 'abcdefg';
SELECT count(id) FROM tbl_main WHERE some_field = 'abcdefg ';

Notice the trailing whitespace in the second query’s only where clause. The field in question is an nvarchar(50) – don’t ask, it was the result of an Access-SQL Server transition made long before I worked here. Both queries return the same count, and the result sets are identical.

Yeahbutwha?

So I run the following query:

1

UPDATE tbl_main SET some_field = ltrim(rtrim(some_field)) WHERE some_field = 'abcdefg ';

And it tells me it updated all of the rows – 821, to be exact. Great. I run my first two queries again, and… Yeah, still 821 rows for both queries. If I change the ‘=’ on the second query to a ‘like’, then it excludes things. This is getting weird. Since I know the value should be 7 characters long (no whitespace included), I run the following:

1

SELECT * FROM tbl_main WHERE some_field = 'abcdefg ' AND len(some_field) <> 7;

And I get nothing back. Searching for a string that’s exactly 8 characters long (with the whitespace), and telling it to show me the results where that field is not 7 characters long returns an empty set. I check again, and every single record without the len() constraint returns a some_field value 7 characters long.

So what’s the cause? I tried adding ten more spaces at the end of the where clause. Still the same result set. I tried to add one at the beginning, and nothing came back. So is this an issue with nvarchar? Or does this happen with varchar in general in SQL Server? This is a SQL Server 2005 environment. I have never seen this behavior before, but I haven’t exactly gone looking for it. One more query, just to show how weird this is:

1

SELECT * FROM tbl_main WHERE assigned_to_cuid LIKE '% ';

In this case, I get nothing back. Seriously, horrifying, terrifying behavior. I’d hate to think that I could so easily return results on queries that I wouldn’t expect to receive given basic rules of string equality. ‘abc ‘ != ‘abc’ – it just doesn’t happen that way. It’s why trim methods were invented. Anyway, let me know if this is a known issue, or if you’ve ever actually seen it before.