What is Order of operation with LTRIM/RTRIM/ISNULL in SQL server?

948    Asked by CelinaLagunas in SQL Server , Asked on Apr 24, 2021

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?

what is sql server rtrim? And what does it does?

Answered by Carl Paige

SQL server rtrim 

In SQL Server RTRIM is a Transact-SQL function that removes all space characters from the right-hand side of a string. SQL Server will evaluate functions inside to out. Your first example is equivalent to: In SQL Server RTRIM is a Transact-SQL function that removes all space characters from the right-hand side of a string. 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 = ''
BEGIN
    SET @Test1 = NULL
END
SELECT @Test1

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

Interviews

Parent Categories