The function REPLACE substitutes one character string for another string in the specified column.
In the following example, the abbreviated notation of street shall be replaced by the complete spelling to obtain a uniform representation.
SELECT hno, city, state, zip
REPLACE (address, 'tr.', 'treet') address
FROM hotel
WHERE address LIKE '%tr'
TRANSLATE replaces single letters in the specified column by other letters. For each occurrence, the ith letter of the first character string is replaced by the ith letter of the second character string. The following statement performs such a replacement that - as we must admit - does not make much sense in the present case.
SELECT name, TRANSLATE (name, 'ae', 'oi') name_new
FROM customer
WHERE firstname IS NOT NULL AND
city = 'Los Angeles'
