Hive provides few functions to handle the string replacement. Using string replace functions , we can replace some characters or the string with another character or string in Hive table values. Lets see the following string functions in detail.

• regexp_replace
• translate

Regexp_replace function in Hive

We need to define the pattern of the string like java regular expression in the Regexp_replace function.If the given string pattern match with the input string, it will replace all the occurrence of string to new string that given as a string replacement value in the function.

Syntax of Regexp_replace function

The initial string is the given string value or the column name, The second argument is the pattern of the string or sub string value. The final argument is the new string value that we want to replace the original string.

Example for Regexp_replace function

In this example, we are going to replace the existing string “college” with new string “University” in the institute column.

1.Replace the single string with another string

Here the regexp_replace function is used to replace the string “college” to “University” in the institute column of student table.

Output:

2.Replace the multiple string with another string

We can mention the multiple existing strings with the pipe line (|) character in the regexp_replace function and the those strings will be replaced with the given new string value. Here the “college” and “institution” strings has replaced with “University” in the institute column of student table.

Output:

Translate function in Hive

The translate function is used to translate the input string by replacing the from string/character to new string/character value.

Syntax for Translate function

The translate function has three arguments as input. The first argument is the input string or column name. The second argument is the from string/character that needs to replace with another string(to string). The third argument is the string that will replace all the occurrence of matching string(from string).

Example for Translate function

1.Replace single character to another character

In this example, we are going to replace all the $(dollar symbol) to | (pipe line symbol) using the Translate function.

• Input String -> Albert$Computer&Science&Engineering$KCTUniversity$
• From String -> $ (Dollar symbol)
• To String -> | (Pipe line symbol)

2.Replace multiple characters to another corresponding character

In this example, we are going to replace all the occurrence of $(dollar) symbol to |(pipe) symbol and all the occurrence of &(ampersand) symbol to space using Translate function.

• Input String -> Albert$Computer&Science&Engineering$KCTUniversity$
  From String -> $& (Dollar and Ampersand symbol)
  To String -> “| ” (Pipe line symbol and space)

3.Mention the column name in the Translate function

We can specify the column name in the first argument of translate function so that it translate the column values as below.

Output

Recommended Articles

• Regexp_extract function in Hive with examples
• RLIKE (Regular expression) function in Hive with examples