In SQL Server 2022, an enhancement came in RTRIM() function, which was a long awaited functionality. This enhancement helps us to remove last character from the string. I have written an article about it in detail.
Recently, I was using RTRIM() function and came across an error as mentioned below.
Error:
Invalid length parameter passed to the rtrim function.
Sample:
USE tempdb
GO
CREATE TABLE #Employees
(
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(250),
EmailAddress VARCHAR(MAX)
);
GO
INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
, ('Sarina','sarina152@gmail.com;')
, ('Sophia','sophiaa123@gmail.com;');
GO
SELECT * FROM #Employees;
GO
Example:
In the given below example, I have written a simple script using RTRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.
USE tempdb GO DECLARE @separator VARCHAR(MAX); SET @separator = ';'; SELECT EmployeeID , EmployeeName , EmailAddress , RTRIM(EmailAddress, @separator) AS EmailAfterRTrim FROM #Employees; GO
Solution:
The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.
USE tempdb GO DECLARE @separator VARCHAR(1); SET @separator = ';'; SELECT EmployeeID , EmployeeName , EmailAddress , RTRIM(EmailAddress, @separator) AS EmailAfterRTrim FROM #Employees; GO
Conclusion:
Whenever you use RTRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length to avoid such error.
SQL Server Portal 