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 (https://blog.sqlauthority.com) , BOL – LEN, BOL – DATALENGTH

, ,
Previous Post
SQL SERVER – Cannot Resolve Collation Conflict For Equal to Operation
Next Post
SQL SERVER – SQL Joke, SQL Humor, SQL Laugh – Funny Quotes

Related Posts

6 Comments. Leave new

  • 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)

    Reply
  • Suresh Reddy
    June 15, 2013 8:15 pm

    We can use ISNULL as well
    DECLARE @MyVar VARCHAR(10)
    SET @MyVar = NULL
    SELECT LEN(ISNULL(@MyVar, ”))

    Reply
  • Looks better (imho) ;
    if isnull( LEN(@MyVar) , 0 ) = 0

    works the same… :)

    Reply
  • 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 :).

    Reply
  • Saadon Motimoti
    February 28, 2020 4:41 pm

    what about date fields?

    DECLARE @MyVar datetime
    SET @MyVar = NULL
    SELECT LEN(ISNULL(@MyVar, ”))

    ?

    Reply
  • If you have a number instead, you should cast it to varchar if you want the lenght, else Len function in Sql Server will tell you that coalesce(youtrInt, ”) is 1 ,and same will do ISNULL:

    declare @myVar int
    SET @myVar = NULL
    SELECT LEN(ISNULL(@myVar, ”))
    =return 1

    –instead
    SELECT LEN(ISNULL(cast(@myVar, varchar(10), ”))
    return 0

    Reply

Leave a Reply

Menu