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.
SQL Server Portal 