Only this week, get the SQL Complete Track of 9 courses in a special prize of $330 $89!
Simple text functions
How to modify Strings
23. The TRANSLATE() function


T-SQL provides another function similar to REPLACE(). It is TRANSLATE(), and it looks like this:

TRANSLATE(StringToSearch, OldCharacters, NewCharacters)

This function searches StringToSearch, which can be an actual string or a field in the database, for the characters in OldCharacters. When it finds them, it replaces them with the corresponding characters in the NewCharacters. Here's how it looks in action:

  TRANSLATE(Text, N' .', N'*!') AS Translation
FROM Slogan;

This will change the text of the slogans in the Slogan table. The characters we're replacing are N' .': spaces and periods. We want to translate them to N'*!': all spaces are changed into asterisks and all dots are changed into exclamation marks. Look at the result:

Id Text Translation
1 Feel the difference. Feel the powder. Feel*the*difference!*Feel*the*powder!
2 Get enthusiastic - even without alcohol. Get*enthusiastic*-*even*without*alcohol!
... ...
8 Each traveller's choice. Each*traveller's*choice!


The marketing boss wants to make some of the slogans more original by replacing exclamation marks with periods and spaces with underscores. Do this for copywriters with an ID of 1, 2, 3, or 4. Show the new slogan text and the first and last name of the copywriter. Name the first column NewSlogan.

Stuck? Here's a hint!

Join two tables: Slogan and Copywriter. Use TRANSLATE(Slogan.Text, ' !' , '_.') as the first column and use Copywriter.Id IN(1, 2, 3, 4) in WHERE.