SQL Server Portal

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.

Read Full Post »