SQL Server Portal

We all have been using RTRIM() function for ages, it helps us to removes the unwanted space(s) character char(32) from the end of any column(s) \ expression(s) in earlier version of SQL Server.

In SQL Server 2022, an enhancement came in RTRIM() function, which was a long awaited functionality. This enhancement will allow us to remove any specific character(s) from the RIGHT sides along with space(s) character char(32).

Let me show you in the below example, how we were using RTRIM() functions to remove space(s) character char(32) ONLY in the earlier version of SQL Server.

Example 1:

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = 'KenSanchez@gmail.com   ';
SELECT  @EMAIL   AS [OriginalEmail]
, RTRIM(@EMAIL)  AS [RTrimEmail];
GO
--OUTPUT

Enhancement:

Now, let me demonstrate this enhancement by giving few examples but before demonstrating this enhancement, your database compatibility level MUST be 160 or higher. Given below is the query to change your database compatibility level.

 ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 160 

Example 2:

In this example, I will show you how to pass the newly introduced parameter in RTRIM() function. This parameter removes spaces(s) / character(s) from the RIGHT side of the column(s) / expression(s) which will be defined in single quotes after RTRIM() as shown below.

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = 'KenSanchez@gmail.com;';
SELECT  @EMAIL       AS [OriginalEmail]
, RTRIM(@EMAIL,'; ')  AS [RTrimEmail];
GO
--OUTPUT

Example 3:

In this example, I will demonstrate that by default RTRIM() function takes spaces(s) as parameter, if you do not pass any parameter as shown in below example :

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '     KenSanchez@gmail.com';
SELECT  @EMAIL   AS [OriginalEmail]
, RTRIM(@EMAIL)  AS [RTrimEmail]
, RTRIM(@EMAIL,' ') AS [RTrimEmailWithParameter];
GO
--OUTPUT

Conclusion:

I found the new enhancement of RTRIM() function very useful, earlier we used multiple functions to find and remove any character(s) from the RIGHT side of the column(s) \ expression(s) but now we can easily achieve with the help of RTRIM() function. Do let me know if you use this function and how you find it.

Read Full Post »