Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in MS SQL Server

Introduction
Concatenation
Simple text functions
How to modify Strings
23. The TRANSLATE() function
Summary

Instruction

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:

SELECT
  Id,
  Text,
  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:

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

Exercise

The marketing boss wants to make some of the slogans more original by replacing periods with exclamation marks 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.