Does the order of operation that you place your LTRIM and RTRIM matter when used in conjunction with ISNULL? For instance, take the following example where a user potentially enters a bunch of spaces in a field, but we trim their input to be an actual NULL value to avoid storing empty strings.

I am performing the TRIM operations outside of ISNULL:

DECLARE @Test1 varchar(16) = '    '

IF LTRIM(RTRIM(ISNULL(@Test1,''))) = ''
    SET @Test1 = NULL


This appropriately returns a true NULL value. Now let's place ISNULL on the outside:

DECLARE @Test2 varchar(16) = '    '

IF ISNULL(LTRIM(RTRIM(@Test2)),'') = ''
    SET @Test2 = NULL


This also returns a NULL value. Both work well for the intended usage, but I'm curious if there is any difference to how the SQL query optimizer handles this?

  • @SQLFox I understand the difference between NULL, an empty string (''), and string literal 'NULL' values. The purpose of my OP is to understand whether the order of operation matters or not when trimming input. If I enter space a couple of times into a web form field, I do not want to store those empty strings. I want to store a NULL column row. By trimming that space (' '), I can evaluate an ISNULL expression. However, that is beyond the scope of the question. Jun 19, 2018 at 22:23
  • 1
    If you want to replace an empty string with NULL, you might try NULLIF(LTRIM(RTRIM(@Test1)),'')
    – SQLFox
    Jun 19, 2018 at 22:33

2 Answers 2


Your tests are redundant. First both LTRIM and RTRIM return NULL when given NULL input:

declare @Test1 varchar(16) = null;

    lft = LTRIM(@test1),
    rgt = RTRIM(@Test1);

lft              rgt
---------------- ----------------
NULL             NULL

Second, standard SQL ignores trailing spaces when comparing strings:

    A =case
        when '' = '   ' then 'equal'
    B = case
        when '  ' = '     ' then 'equal'

A     B
----- -----
equal equal

SQL Server provides the NULLIF function. This takes two parameters. If they differ the first will be returned. If they are equal it will return NULL. I think this will address your requirement.

declare @Test1 varchar(16) = '    ';

    first = nullif(@Test1, '');

set @Test1 = NULL;

    second = nullif(@Test1, '');

set @Test1 = 'some value';

    third = nullif(@Test1, '');



some value

You may still need the LTRIM, depending on your input validation.

  • Upvote simply for the fact that "standard SQL ignores trailing spaces when comparing strings". Thank you! Saved me a ton of time. Although, surprised to find that out. Definitely some implicit logic that might not be desired in some instances. Nov 17, 2023 at 11:04

SQL Server will evaluate functions inside to out. Your first example is equivalent to:

DECLARE @Test1 varchar(16) = '    '

SET @Test1 = ISNULL(@Test1,'')
SET @Test1 = RTRIM(@Test1)
SET @Test1 = LTRIM(@Test1)

IF @Test1 = ''
    SET @Test1 = NULL


Since ISNULL just returns the first non-NULL value, the function just returns @Test1. It then goes on to trim whitespace. If you reverse the order of these it's going to act the exact same way because ISNULL will always evaluate to FALSE.

For your described purpose, the order is largely irrelevant. The ISNULL function is only needed for situations where you don't care about trimming whitespace, and vice versa (as LTRIM/RTRIM do nothing to NULL values).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.