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.
Examples:
SELECT REPLACE('MySQL is a free software', 'MySQL', 'Oracle MySQL') As
Txt;
Result:
Txt
-----------------
Oracle MySQL is a free software
Oracle MySQL is a free software
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
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;
No comments:
Post a Comment