TRANSLATE & REPLACE functions in Cloudera’s Impala & Apache Hive

This article introduces the new TRANSLATE and REPLACE string functions available from version 2.9.0 of Cloudera Impala. Both of these functions look identical, shifting letters from one to the other. There is a major difference between them, however.

Let’s see how close these functions are:

SELECT REPLACE (‘Flat_720’, ‘_’, ‘#’);
SELECT TRANSLATE (‘Flat_720′,’_’,’#’);

Result:
Both returns “Flat#720′

Let’s see what documentation says about these functions.

The purpose of the ‘REPLACE’ function is that it returns the initial argument with all occurrences of the target string replaced by the replacement string.

Let’s dig into more.

This is equivalent to the REPLACE function that is available in most of the RDBMS platforms (MySQL, Oracle & SQL Server). The matching is case-sensitive. If any argument is NULL, the return value is NULL.

Example1:
SELECT REPLACE(‘MySQL is a free software’, ‘MySQL’, ‘Oracle MySQL’) As Txt;

Result:
Txt
—————–
Oracle MySQL is a free software

Example2:
SELECT REPLACE(‘obrocodobro’,’o’,’a’) AS replaced_string;

Result:
replaced_string
——————–
abracadabra

If no match found, the original string is returned unchanged.

SELECT REPLACE(‘SQL Bank’, ‘Code’,’Repository’) As Output;

Result:
Output

———

‘SQL Bank’

TRANSLATE: Returns the input string with each character in the from argument replaced with the corresponding character in the to argument. The characters are matched in the order they appear in from and to.

Example:

SELECT TRANSLATE (‘hello world’,’world’,’earth’) as Output

Result:
Output
———————
hetta earth

It translates letter by letter. If you look at the above example, the word “world” is replaced by “earth”. In addition, it replaced the fourth letter ‘L’ from the string “world” to the fourth letter “T” from the word “earth”. The change is applied to the entire string rather than just the input string. Similarly, the second letter ‘o’ is replaced from “world” to the second letter ‘a’ from ‘earth’. With this example, it is obvious that we cannot use ‘TRANSLATE’ instead of ‘REPLACE.’

Where we use ‘TRANSLATE’ then? Let’s see.

SELECT TRANSLATE(‘3*[2+1]/{8-4}’, ‘[]{}’, ‘()()’) as Output;

Result:
Output
——————-
3*(2+1)/(8-4)