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

4 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

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