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! |
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.
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)
