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)
 
 
translate_function-1

Note:
These functions are available in Apache Hive too and there’s no difference in its functionality behaviour.

Hope you liked this post.

Please click on the follow button to receive updates on the latest posts.

 
 

4 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s