What is the order of operation with LTRIM/RTRIM/ISNULL?

Question

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 in how the SQL query optimizer handles this? What sql server rtrim
used for?

Ankesh Kumar · Accepted Answer

SQL Server RTRIM SQL Server RTRIM function removes all space characters from the right-hand side of a string. 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.

What is the order of operation with LTRIM/RTRIM/ISNULL?

About this question

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

SQL Server Tutorials

SQL Server Interview Q&A

Latest SQL Server Blogs

Want answers to more SQL Server questions?

Stay in the loop — it’s free

You’re on the list!