Order of operation with LTRIM/RTRIM/ISNULL

by PicoDeGallo   Last Updated June 19, 2018 22:06 PM

Does the order of operation that you place your LTRIM and RTRIM matter when used in conjunction of 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?



Related Questions





SQL Server Actual Plan. Incorrect row estimates

Updated September 28, 2017 14:06 PM

Need your help in query tuning

Updated June 19, 2015 09:02 AM