SQL Server Portal

As we all know, TRIM() function removes the extra space(s) character char(32) from the start & end of any column(s) \ expression(s) in earlier version of SQL Server.

In SQL Server 2022, an enhancement is available in TRIM() function, which was a long awaiting functionality. This enhancement will allow us to remove any specific character(s) from one of the sides (left, right) or both sides of the column(s) \ expression(s) along with space(s) character char(32).

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

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '     KenSanchez@gmail.com      ';

SELECT  @EMAIL   AS [OriginalEmail]
, TRIM (@EMAIL)  AS [TrimEmail];
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 

The enhancement in TRIM() function provides three new options as shown below.

• Leading
• Trailing
• Both

Let me explain these options in detail.

• Leading

I will demonstrate to you how to use LEADING option in TRIM() function. This option removes space(s) / character(s) from the LEFT side of the column(s) / expression(s) which will be defined in single quotes after LEADING option, as shown in given below example.

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '  ;KenSanchez@gmail.com';

SELECT @EMAIL  AS [OriginalEmail]
     , TRIM(LEADING' ; ' FROM @EMAIL) AS  [AfterTrimEmail];
GO
--OUTPUT

• Trailing

In this example, I will show you how to use TRAILING option in TRIM() function. This option removes space(s) / character(s) from the RIGHT side of the column(s) / expression(s) which will be defined in single quotes after TRAILING option, as shown below.

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = 'KenSanchez@gmail.com;   ';

SELECT @EMAIL  AS [OriginalEmail]
     , TRIM(TRAILING' ; ' FROM @EMAIL) AS  [AfterTrimEmail];

GO
--OUTPUT

• Both

In this example, I will show you how to use BOTH option in TRIM() function. This option removes space(s) / character(s) from BOTH (left & right) sides of the column(s) / expression(s) which will be defined in single quote after BOTH option, as shown below.

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '   ;KenSanchez@gmail.com;   ';

SELECT @EMAIL  AS [OriginalEmail]
     , TRIM(BOTH' ; ' FROM @EMAIL) AS  [AfterTrimEmail];

GO
--OUTPUT

DECLARE @EMAIL VARCHAR(100) 
SET @EMAIL = '   ;KenSanchez@gmail.com;   ';

SELECT @EMAIL  AS [OriginalEmail]
     , TRIM(' ; ' FROM @EMAIL) AS  [AfterTrimEmail];

GO
--OUTPUT

• Note:

If you don’t write any option name (LEADING, TRAILING, BOTH) in the TRIM() function then by default BOTH option will be applied as shown below.

Conclusion:

I found the new enhancement very useful and handy, earlier we used multiple functions to find and remove any character(s) from the start or end of the column(s) \ expression(s) or both sides of the column(s) \ expression(s), now we can achieve it via TRIM() function ONLY. Do let me know if you use this enhancement and how helpful you find it.