Logging Experiences

T-SQL: checking if a string is empty or white-space

Posted in Programming, SQL, Tips and Tricks by Sina Iravanian on September 6, 2012

Consider a case where you want to bring through a varchar column called SomeColumn from table SomeTable and replace its value with, 'Not Available' if the value is NULL or an empty string. This is how it can be achieved in Transact-SQL:

SELECT ISNULL(NULLIF(SomeTable.SomeColumn, ''), 'Not Available')
FROM SomeTable
...

The above code is checking for null or emptiness of the string. To check for being null or white-space, use the following code instead:

SELECT ISNULL(NULLIF(LTRIM(RTRIM(SomeTable.SomeColumn)), ''), 'Not Available')
FROM SomeTable
...
Advertisements
Tagged with: , ,

5 Responses

Subscribe to comments with RSS.

  1. never cold call again ebook free download said, on February 25, 2013 at 8:59 pm

    Having read this I believed it was extremely enlightening.
    I appreciate you taking the time and effort to put this article together.

    I once again find myself spending a lot of time both reading and commenting.
    But so what, it was still worthwhile!

  2. www.online-dating-automator.com said, on April 9, 2013 at 3:09 am

    I am saving your internet site for near future studying!
    !

  3. maassql said, on August 7, 2014 at 3:41 pm

    The term ‘whitespace’ in this case is incorrect. LTRIM and RTRIM remove spaces, not vertical space and not space other than a space, which is horizontal. When we say whitespace, we always mean more than spaces. Here is a demonstration:

    declare @tab char(1) = char(9);
    declare @cr char(1) = char(13)
    declare @lf char(1) = char(10)
    declare @crlf char(2) = @cr + @lf
    declare @space char(1) = char(32)

    /* prove it */
    print ‘–>’ + @tab + ” + @cr + ” + @lf + ” + @crlf + ” + ” + @tab + ” + @cr + ” + @lf + ” + @crlf + ” + @space + ” + ” + ISNULL(NULLIF(LTRIM(RTRIM(@tab)), ”), ‘Not Available’) + ” + ISNULL(NULLIF(LTRIM(RTRIM(@cr)), ”), ‘Not Available’) + ” + ISNULL(NULLIF(LTRIM(RTRIM(@lf)), ”), ‘Not Available’) + ” + ISNULL(NULLIF(LTRIM(RTRIM(@crlf)), ”), ‘Not Available’) + ” + ISNULL(NULLIF(LTRIM(RTRIM(@space)), ”), ‘Not Available’) + ‘<–') as [space]

    /*
    http://msdn.microsoft.com/en-us/library/ms177827.aspx
    Returns a character expression after it removes leading blanks.
    */

  4. maassql said, on August 7, 2014 at 3:42 pm


    eclare @tab char(1) = char(9);
    declare @cr char(1) = char(13)
    declare @lf char(1) = char(10)
    declare @crlf char(2) = @cr + @lf
    declare @space char(1) = char(32)

    /* prove it */
    print '-->' + @tab + '' + @cr + '' + @lf + '' + @crlf + '' + '' + @tab + '' + @cr + '' + @lf + '' + @crlf + '' + @space + '' + '' + ISNULL(NULLIF(LTRIM(RTRIM(@tab)), ''), 'Not Available') + '' + ISNULL(NULLIF(LTRIM(RTRIM(@cr)), ''), 'Not Available') + '' + ISNULL(NULLIF(LTRIM(RTRIM(@lf)), ''), 'Not Available') + '' + ISNULL(NULLIF(LTRIM(RTRIM(@crlf)), ''), 'Not Available') + '' + ISNULL(NULLIF(LTRIM(RTRIM(@space)), ''), 'Not Available') + '<--') as [space]

    /*
    http://msdn.microsoft.com/en-us/library/ms177827.aspx
    Returns a character expression after it removes leading blanks.
    */

  5. joshuadotedu said, on February 3, 2016 at 10:11 pm

    I have discovered that SQL Server considers ” (empty string) and ‘ ‘ (a single space character) as equal, so

    select nullif(‘ ‘, ”)

    returns NULL, and

    nullif([SomeColumn], ”)

    returns NULL if [SomeColumn] is truly empty or contains a single space. In fact, a string consisting only of spaces is considered equal to ”. One would have to use the LEN function to tell them apart.

    I haven’t tested other whitespace characters.


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

%d bloggers like this: