By default, there is no Hive replace function available. 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 junk value. In this article, we will check what are Hive replace function alternative methods that you can use whenever required.
Hive Replace Function
As mentioned earlier, Apache Hive does not provide support for replace function. However, it does provides support for regular expression functions and translate function. You can use any of these function as an alternative to the replace function.
Let us check these two functions and how to use them as an alternative method to replace string values.
Hive Translate Function
The Hive translate function translates the input string by replacing the characters present in the from string with the corresponding characters in the to string.
This is similar to the translate function in PostgreSQL. If any of the parameters to this UDF are NULL, the result is NULL as well. This function is available as of Hive 0.10.0, for string types.
Related Articles,
Hive Translate Function Syntax
Below is the syntax for Hive translate function:
translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)For example, Consider if you want to translate junk values such as ‘#’ from the input string, you can use translate to do your job.
> select translate('This #is test to verify# translate #Function in Hive', '#','');
+----------------------------------------------------+--+
| _c0 |
+----------------------------------------------------+--+
| This is test to verify translate Function in Hive |
+----------------------------------------------------+--+
1 row selected (0.28 seconds)As you can see in above example, translate function does what replace function could do.
Related Articles,
Hive REGEXP_REPLACE Function
Searches a string for a regular expression pattern and replaces every occurrence of the pattern with the specified replacement.
For example, replace ‘^’ with ‘$’, use below example.
> select regexp_replace('HA^G^FER$JY',"\\^","\\$");
+--------------+--+
| _c0 |
+--------------+--+
| HA$G$FER$JY |
+--------------+--+
1 row selected (0.208 seconds)You can read more about Hive regular expression in my other post, Hadoop Hive Regular Expression Functions and Examples
Hope this helps 🙂