SDU Tools: Translate

I love it when SQL Server gets brand new T-SQL language features.

SQL Server 2017 added a great new language feature called TRANSLATE. It’s been in some other products like Oracle for a while but it’s just been added to SQL Server.

Imagine I have a string like this:

’[02] 9992:2343’

But what I really want is one like this:

’(02) 9992-2343’

Up to SQL Server 2016, I could do this with a series of replace statements:

DECLARE @Value varchar(40) = ‘[02] 9992:2343’;

SELECT REPLACE(REPLACE(REPLACE(@Value, ‘[’, ‘(’), ‘]’, ‘)’), ‘:’, ‘-’);

And so on. But if all I want to do is to provide a series of characters that need to be replaced, and a series of replacement characters, TRANSLATE does this nicely:

DECLARE @Value varchar(40) = ‘[02] 9992:2343’;

SELECT TRANSLATE(@Value, ‘[]:’, ‘()-’);

It replaces all characters in the second parameter string with the one in the same position of the third character string.

It’s documented here:

https://docs.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql

But what if you’re not going to be running SQL Server 2017 any time soon I hear you ask? No problems, we have you covered. We added a Translate function to our SDU Tools and it works from SQL Server 2008 onwards.

DECLARE @Value varchar(40) = ‘[02] 9992:2343’;

SELECT SDU_Tools.Translate(@Value, ‘[]:’, ‘()-’);

You can see it in action here: