SQL Server Portal

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

Conclusion:

In case of this error, we should use REPLACE() function instead of TRANSLATE() function.