How to use Impala Replace Function and Examples

The latest version of the Cloudera supports Impala replace function. String manipulation function replace is very much needed in case if you are manipulating strings and when there is a need to replace the particular value. Value could be a junk value or any other values based on your requirements. In this article, we will check Impala replace function and alternative methods that you can use whenever required.

Impala Replace Function

As mentioned earlier, the latest version of Cloudera impala does provide support for replace function. The syntax and usage is similar to replace function available in any relational database systems.

Impala Replace Syntax

Below is the replace syntax:

REPLACE(STRING initial, STRING target, STRING replacement)

Replace function returns the initial argument with all occurrences of the target string replaced by the replacement string.

Impala Replace Examples

Below example demonstrates the usage of replace function in Impala.

[quickstart.cloudera:21000] > select replace('hello world', 'world', 'earth');
Query: select replace('hello world', 'world', 'earth')
...
+------------------------------------------+
| replace('hello world', 'world', 'earth') |
+------------------------------------------+
| hello earth                              |
+------------------------------------------+
Fetched 1 row(s) in 0.13s

[quickstart.cloudera:21000] > select replace('hello worlx','x','d');
Query: select replace('hello worlx','x','d')
...
+----------------------------------+
| replace('hello worlx', 'x', 'd') |
+----------------------------------+
| hello world                      |
+----------------------------------+
Fetched 1 row(s) in 0.12s

Because this function does not use any regular expression patterns, it is typically faster than regexp_replace() for simple string substitutions.

Impala Replace Alternatives

There are some alternative methods that you can use if you are using a lower version of Cloudera impala.

regexp_replace Function
Translate Function

Now let us check example of using regular_replace and Translate function.

regexp_replace Function to Replace Values

Returns the initial argument with the regular expression pattern replaced by the final argument string.

regexp_replace Syntax

Below is the syntax;

regexp_replace(string initial, string pattern, string replacement)

regexp_replace Example

Below are some examples to demonstrate regexp_replace function.

[quickstart.cloudera:21000] > select regexp_replace('111-222-333','[^[:digit:]]','');
Query: select regexp_replace('111-222-333','[^[:digit:]]','')
...
+---------------------------------------------------+
| regexp_replace('111-222-333', '[^[:digit:]]', '') |
+---------------------------------------------------+
| 111222333                                         |
+---------------------------------------------------+
Fetched 1 row(s) in 0.12s

Impala Translate Function

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.

Impala Translate Function Syntax

Below is the Impala translate function syntax.

translate(string input, string from, string to)

Impala Translate Function Examples

Below are some examples to demonstrate Impala translate function.

[quickstart.cloudera:21000] > select translate('Number#2', '#', '_');
Query: select translate('Number#2', '#', '_')
...
+---------------------------------+
| translate('number#2', '#', '_') |
+---------------------------------+
| Number_2                        |
+---------------------------------+
Fetched 1 row(s) in 0.12s

In above example, translate replaces ‘#’ with ‘_’.