TRANSLATE()–Replace multiple characters in single function call

TRANSLATE() is a new SQL Server 2017 string function which supports replacing multiple characters in one function call, This will help us in avoid using multiple replace statements


TRANSLATE(InputString, StringPattern, StringReplacement)

Example Usage

In this example, we have values delimited by , for field terminator and ~ for row terminator, which we are going to replace , (comma) with | (pipe) and ~ (Tilde) with # (Hash)

DECLARE @strInput VARCHAR(255) = ‘Test1,Test2,Test3,Test4,Test5~Test11,Test12,Test13,Test14,Test15~Test21,Test22,Test23,Test24,Test25’

SELECT @strInput [Input],
REPLACE(REPLACE(@strInput, ‘,’, ‘|’), ‘~’, ‘#’) [SQL 2016 & Before – Output],
TRANSLATE(@strInput, ‘,~’, ‘|#’) [SQL 2017 Output]



As you can see, in SQL Server 2016 and prior, we need to use 2 REPLACE() function calls, in SQL Server 2017, we can use 1 TRANSLATE() function call to implement the same functionality

Hope you find this post useful !!!


Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: