Order of operation with LTRIM/RTRIM/ISNULL

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,''))) = ''
BEGIN
    SET @Test1 = NULL
END

SELECT @Test1

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

DECLARE @Test2 varchar(16) = '    '

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

SELECT @Test2

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?

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

declare @Test1 varchar(16) = null;

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

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

Second, standard SQL ignores trailing spaces when comparing strings:

select
    A =case
        when '' = '   ' then 'equal'
    end,
    B = case
        when '  ' = '     ' then 'equal'
    end;

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) = '    ';

select
    first = nullif(@Test1, '');

set @Test1 = NULL;

select
    second = nullif(@Test1, '');

set @Test1 = 'some value';

select
    third = nullif(@Test1, '');

first
----------------
NULL

second
----------------
NULL

third
----------------
some value

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

StackExchange Database Administrators Q#210066, answer score: 7