The (In)Equality of the spaces

Today I’d like to talk about an issue I ran into over the past week… It isn’t the first time that I have seen this, but it apparently didn’t sink in well enough the last time.  The issue has to do with how SQL interprets space at the end of a string when dealing with the equality operator.  One might expect the space to be counted when determining the equality of a string – it is, after all, being stored in the database.  And this is the case… kind of.

Lets look at a simple example:

 Declare @TestString as varchar(10) = ‘blah ‘;

Select case when @TestString = ‘blah’ then 1 else 0 end as myTest;

——-

Result 1

Here we have an trailing space on the variable.  I would probably expect this to return 0.  It does not.  We ignore the trailing space when it comes to equality.

Let’s look at a leading space.

Declare @TestString as varchar(10) = ‘ blah’;

Select case when @TestString = ‘blah’ then 1 else 0 end as myTest;

——-

Result 0

OK, this works as I would expect.  It is not, in fact, equal.  We treat spaces anywhere, other then trailing, as non-equal if present only on one side of the comparison.

Now, the real concern I ran into this week was not really having to do with either of these cases, but instead in the difference between how we interpret the trailing space with an equality, and what I would also consider to be an equality, the like operator with no wildcards.

Declare @TestString as varchar(10) = ‘blah ‘;

Select case when @TestString like ‘blah’ then 1 else 0 end as myTest;

——-

Result 1

Again, I’m a little surprised at the results here as we come back with a match.  It ignores the trailing space.  Now let’s reverse the assignment.

Declare @TestString as varchar(10) = ‘blah’;

Select case when @TestString like ‘blah ‘ then 1 else 0 end as myTest;

——

Result 0

Here we have the trailing space in the like clause.  No dice.

So, what have we learned?  I have no clue. I can say that there is an inequality in the way that we treat spaces with comparison operators in the database.  If you are finding that when auditing data you have differences in the counts, make sure that you are using the same operators, and that they are in fact doing what you would expect.  Particularly if you are storing patterns for comparison with a like operator, some of which contain wildcards and some of which do not.