replacing multiple units of specific character with one unit in hive

Question

I have a dataset in which values are same except the number of semicolons in it resulting to different records.

For example if in a column one records has a;b;c and another record has a;;b;c, this is disabling the use of distinct function in my code. I want this to be treated as duplicate record for which ;; needs to be replaced with ;

How can we replace multiple ; with single ; in strings in a dataset in hive?

score 0 · Accepted Answer · answered Sep 06 '17 at 09:47

0

You can use regexp_replace as defined in Hive UDFs

The first argument is the string that needs to get changed. So you can call it on your table like :

with t as 
   (SELECT "a\;\;\;b\;\;c\;d" as col ) 
SELECT regexp_replace(t.col, "\;+", "\;") as col from t

This should give you the output

+-------+
|    col|
+-------+
|a;b;c;d|
+-------+

answered Sep 06 '17 at 09:47

philantrovert

9,904
3
37
61

I used it this way - regexp_replace(study.trial_result, ";+| ;+", ";") – Vaibhav Sep 06 '17 at 10:19
I had to escape `;` but `;+` already means one or more occurrences of `;` – philantrovert Sep 06 '17 at 10:20

replacing multiple units of specific character with one unit in hive

1 Answers1