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 documentationtranslations 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.
We have been using REPLACE() function for ages in SQL Server whenever we need to replace any characters in a string but the problem arises when we need to replace multiple characters with multiple characters. In such cases we had to use REPLACE() function multiple times to achieve it till TRANSLATE() function came in picture in SQL Server 2017.
Compatibility Level:
Your database compatibility level MUST be 140 or higher to use TRANSLATE() function. Given below is the query to change your database compatibility level.
ALTER DATABASE Northwind SET COMPATIBILITY_LEVEL = 140
Let me demonstrate, how we were using REPLACE() function in earlier version of SQL Server:
Example 1: (Old approach using REPLACE() function)
In the given below Example, we used REPLACE() function twice in order to replace multiple characters in a string.
DECLARE @Number VARCHAR(25)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
, REPLACE(REPLACE(@Number,'+',' '),'-',' ') AS ReplacedPhoneNumber;
GO
--OUTPUT
Example 2: (New approach using TRANSLATE() function)
In new approach, we can achieve the same output using TRANSLATE() function and we do not need to write function twice.
DECLARE @Number AS VARCHAR(25)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
, TRANSLATE(@Number,'+-',' ') AS TranslatedPhoneNumber
GO
--OUTPUT
Now, we know how to use these functions (TRANSLATE() and REPLACE()). Let me show you how we can use it in query having tables.
Example 3: (Old approach using REPLACE() function)
In the given below example, we are going to replace ( , ),- with spaces using REPLACE() function.
USE Northwind
GO
SELECT FirstName
, LastName
, Title
, HomePhone
, REPLACE(REPLACE(REPLACE(HomePhone,'(',''),')',' '),'-',' ') AS ReplacedHomePhone
FROM [dbo].[Employees] ;
GO
--OUTPUT
Example 4: (New approach using TRANSLATE() function)
In the given below example, we are going to replace ( , ),- with spaces using TRANSLATE() function.
USE Northwind
GO
SELECT FirstName
, LastName
, Title
, HomePhone
, TRANSLATE(HomePhone,'()-',' ') AS TranslatedHomePhone
FROM [dbo].[Employees] ;
GO
--OUTPUT
Conclusion:
I used TRANSLATE() function and found it very handy, the only limitation I can see is that the characters and translations should be the same size. If not it will generate error which we will discuss in upcoming articles. Do let know if you used this function and found it useful.
This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.