SQL Server Portal

Removing the last character in a string has never been easier before SQL Server 2022 version. I had written a detailed article back in 2013 regarding this issue, where I used, STUFF(), LEN() functions etc.

In SQL Server 2022, a new enhancement came in TRIM() & RTRIM() function, where we can define any characters to be removed from the String. I had written a detailed article about it earlier. By using above mentioned functions, we can simply remove the last character from the string.

Compatibility Level:

Database compatibility level MUST be 160 or higher to use New Approaches. Given below is the query to change your database compatibility level.

ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 160 

Let me demonstrate, how to remove last character from string in the following examples.

Example 1:

• Old Approach:

In the old approach, we have to use multiple functions (STUFF(), LEN()) in order to remove the last character from string as shown below.

DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
 
SELECT @String AS String
     , STUFF(@String,LEN(@String), 1, '') AS LastCharacterRemoved;
GO
--OUTPUT

• New Approach:

• Using TRIM() function :

In the new approach, its quite simple, we can remove the last character from string by using TRIM() function as shown below.

DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
 
SELECT @String AS String
     , TRIM(TRAILING';' FROM @String) AS LastCharacterRemoved;
GO
--OUTPUT

• Using RTRIM() function:

In the new approach, we can remove the last character from string by using RTRIM() function as shown below.

DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
 
SELECT @String AS String
     , RTRIM(@String,';') AS LastCharacterRemoved;
GO
--OUTPUT

Example 2:

• Old Approach:

In the old approach, we had to use multiple functions (STUFF(), LEN()) in order to remove the last character from string as shown below.

USE Northwind
GO

SELECT EmployeeID
     , FirstName
     , LastName
     , Title
     , TitleOfCourtesy
     , STUFF(TitleOfCourtesy 
     , LEN(TitleOfCourtesy), 1, '') AS LastCharacterRemoved
FROM Employees;
GO
--OUTPUT

• New Approach:

• Using TRIM() function:

In the new approach, its quite simple, we can remove the last character from string by using TRIM() function as shown below.

USE Northwind
GO

SELECT EmployeeID
     , FirstName
     , LastName
     , Title
     , TitleOfCourtesy 
     , TRIM(TRAILING '.' FROM TitleOfCourtesy) AS LastCharacterRemoved 
FROM Employees;
GO
--OUTPUT

• Using RTRIM() function:

In the new approach, we can remove the last character from string by using RTRIM() function as shown below.

USE Northwind
GO

SELECT EmployeeID
	 , FirstName
	 , LastName
	 , Title
	 , TitleOfCourtesy 
	 , RTRIM(TitleOfCourtesy,'.') AS LastCharacterRemoved
FROM Employees;
GO
--OUTPUT

Conclusion:

In SQL Server 2022 or above, removing the last character from the string is quite easy, since there are multiple functions available to achieve it as shown above. Do let me know which approach are you using and how did you find it.