Equality and trailing spaces in SQL

While reading posts on a forum, my attention was caught by a SQL oddity causing equality comparisons on strings with trailing spaces to possibly return unexpected results.

For example,
‘abc’ = ‘abc         ‘
will be considered to be true.

A fellow blogger provides more information about this.

The keyword for this behavior is “padding”.

To keep it simple, before a comparison between two strings is made, the server will add trailing spaces to the shorter one to make sure both strings have the same size. According to the post mentioned above, this is standard compliant.

More information can also be found in this article (MS knowledge base).

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: