Autumn Offers - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Concatenation
Simple text functions
Modifying strings
18. The TRANSLATE() function
Summary

Instruction

PostgreSQL provides another function similar to REPLACE(). It's TRANSLATE(), and it looks like this:

TRANSLATE(string_to_search, old_characters, new_characters)

This function searches string_to_search, which can be an actual string or a field in the database, for the characters in old_characters. When it finds them, they're replaced with the corresponding characters in the new_characters. Here's how it looks:

SELECT
  id,
  text,
  TRANSLATE(text, ' .', '*!') AS translated_text
FROM slogan;

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

id text translated_text
1 Feel the difference. Feel the powder. Feel*the*difference!*Feel*the*powder!
2 Get enthusiastic - even without alcohol. Get*enthusiastic*-*even*without*alcohol!

Exercise

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 new_slogan.

Stuck? Here's a hint!

Join two tables: slogan and copywriter.
Use the following expression as the new_slogan column:

TRANSLATE(slogan.text, '! ' , '._')

Use the following condition in the WHERE clause.

copywriter.id IN (1, 2, 3, 4)