Home > SQL Server, SQL Server 2017 > TRANSLATE()–Replace multiple characters in single function call

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

Syntax

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]

Output

capture20171006114812618

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 !!!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: