Msg 9828 | SQL Server Portal

Posts Tagged ‘Msg 9828’

SQL Server – Fix – The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

Posted in Errors, SQL SERVER, tagged Msg 9828, REPLACE(), SQL Server, T-SQL, The second and third arguments of the TRANSLATE built-in function, TRANSLATE() function on November 14, 2022| Leave a Comment »

TRANSLATE() is one of the handy functions of SQL Server when it comes to replace one or more characters to another set of characters in one go. I have earlier written an article about it in detail.

Recently, I was using TRANSLATE() function and came across an error as mentioned below.

Error :

The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.


DECLARE @Number AS VARCHAR(15)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
     , TRANSLATE(@Number,'+','00') AS TranslatedPhoneNumber;

GO
--OUTPUT

Solution:

As per SQL Server documentation translations must be the same data type and length as characters. And if you look at our script, the length of both characters (+) and translation (00) is not same.

In such cases, we need to simply apply REPLACE() function since TRANSLATE() function cannot handle such cases as shown below.


DECLARE @Number AS VARCHAR(15)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
     , REPLACE(@Number,'+','00') AS REPLACEDPhoneNumber;
GO
--OUTPUT