SQL SERVER – LEN and DATALENGTH of NULL Simple Example

Simple but interesting – In recent survey I found that many developers making this generic mistake. I have seen following code in periodic code review. (The code below is not actual code, it is simple sample code)
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)


I decided to send following code to them. After running the following sample code it was clear that LEN of NULL values is not 0 (Zero) but it is NULL. Similarly, the result for DATALENGTH function is the same. DATALENGTH of NULL is NULL.

Sample Test Version:
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)
PRINT 'LEN of NULL is 0'
ELSE
PRINT
'LEN of NULL is NULL'

Result Set:
LEN of NULL is NULL

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL – LEN, BOL – DATALENGTH

5 thoughts on “SQL SERVER – LEN and DATALENGTH of NULL Simple Example

  1. Hi,
    On googling this problem i was lead to your site.. and you have correctly identified the problem.
    To help out other visitors, the solution is simply COALESCE the value before checking the LEN

    eg:
    DECLARE @MyVar VARCHAR(10)
    SET @MyVar = NULL
    IF (LEN(COALESCE(@MyVar, ”)) = 0)

    Like

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

  3. Len should work on the string instead of null so first replacing NULL with empty string and then performing length operation seems good choice IMHO :).

    Like

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