Doing Something with the REPLACE function

Most of us have used the REPLACE function several times in our daily lives. This function, as you may remember, takes one string and replaces it with another, as seen below:

There is another function within SQL Server that many people think does the same thing, but with a slight nuance.

Sometimes, you just need to change out one character with another. For example, you need to make a “(” into a “[” to make everything consistent.

With the TRANSLATE function, we can make this change in a few keystrokes.

SELECT Venue, 
       TRANSLATE(Venue, '(', '[') AS CleanVenue
FROM dbo.NirvanaPerformances
WHERE Venue LIKE '%(%'

Now, you’re probably thinking “Sherpa, I can do that with the REPLACE function just like that. Why do I need another function?”

I’m so glad you asked!

What happens with REPLACE if you’re now asked to also change the “]” character into “)”? You’ll have to do something like this:

SELECT Venue, 
  REPLACE(REPLACE(Venue, '(', '['), 
                         ')', ']') AS CleanVenue
FROM dbo.NirvanaPerformances
WHERE Venue LIKE '%(%'

With each new character that is added, you have to add more REPLACEs. I don’t know about you, but after 2 of these my eyes glaze over and I don’t want to look at it anymore.

But with the TRANSLATE function, I can do the following:

SELECT Venue, 
   TRANSLATE(Venue, '()', '[]') AS CleanVenue
FROM dbo.NirvanaPerformances
WHERE Venue LIKE '%(%'

Not all is wine and roses with the TRANSLATE function though.

1. The number of characters in the second parameter – in this case, the ‘()’ – must equal the number of characters in the third parameter – the ‘[]’. You will get a spiffy error message if you forget:

2. You can not change one character into a NULL string without making everything into a NULL string. For example, I can’t expect the following to work:

SELECT Venue, 
  TRANSLATE(Venue, '[]', '(' + NULL) AS CleanVenue
FROM dbo.NirvanaPerformances
WHERE Venue LIKE '%(%'

This actually will return all NULLs for the CleanVenue column even if there are no “[” or “]” to translate!

3. TRANSLATE will literally translate every character into another character. If I wanted to exchange the letters in the string “LAME” for “DUCK”, I should probably be using the REPLACE function instead.

And that my friends, is the TRANSLATE function! As always, use it wisely and check that it works the way you expect it to! Until next time!