SQL – String comparison

Posted on 29/08/2008. Filed under: Software Development |

Where is all my data!

When applying a filter condition on you select statement using a string comparison, be very aware of the fact that you may not include null values in your result set.
SQL Server 2005 will not include null values in the comparison when you filter on string values.
Below is an example SQL statement:

Select name, surname
from tableA
where name ‘John’;

If the “name” column in “tableA” does contain null values, these rows will not be part of your result set. I guess this is because the SQL Server 2005 engine does not consider null as a string, hence it is not included in the comparison.

If your functional requirement is:
Do not return values which is equal to “John”, you would have to do a null check in addition to the string comparison.
Here is an example:

Select name, surname
from tableA
where isnull(name, ”) ‘John’;


If your source data permits, the best solution would be to make this column not nullable!

Advertisements

Make a Comment

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

  • Enter your email address to follow this blog and receive notifications of new posts by email.

  • Who’s visiting

  • South Africa

  • Tweets

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: