Q&A: Why does WHERE LEN(columnname) = 0 skips NULL values?

SQL Server 2000

Q: When I use the condition LEN(columnname) = 0, I don't get columns where columnname value is null. Why? Shouldn't LEN(NULL) = 0 ? Are my ANSI_NULLS or ANSI_DEFAULTS setting to blame?

A: No. LEN(NULL) will always return NULL. From SQL Server Books Online Null Values

A value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
...
When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE.

Ansi settings don't have any affect on this behaviour. You can check this with:

SET ANSI_DEFAULTS on
SELECT LEN(NULL), LEN('')
SET ANSI_DEFAULTS off
SELECT LEN(NULL), LEN('')
Both queries return the same results:
------------ ------------
NULL         0

(1 rows(s) affected)


------------ ------------
NULL         0

(1 rows(s) affected)

To correctly identify if your column contains a zero-width string or NULL, you can use either of these two sets of condtions:

WHERE LEN(columnname) = 0 OR columnname IS NULL
or
WHERE LEN(COALESCE(columnname,'')) = 0



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Q&A: Why does WHERE LEN(columnname) = 0 skips NULL values? | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.