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.

About these ads
This entry was posted in SQL and tagged . Bookmark the permalink.

2 Responses to The (In)Equality of the spaces

  1. Sugand Dhem says:

    Try all the above with using char(10) as opposed to varchar(10)

    • Eric Wisdahl says:

      Yes, with char this would work (although more than likely a lot of the rest of your application would not). The point to this was that it is not the same with Varchar, which from my experience is the predominant data type used. Oh, and that it completely caught me off guard.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s