Search code examples
hiveprestotrino

Equivalent of Presto REPLACE function in Hive

I have a condition currently in presto like below

replace(replace(replace(replace(replace(replace(replace(replace(UPPER(FIELDNAME),' ',''),'LLC',''),'INC',''),'INTERNATIONAL',''),'LTD',''),'.',''),',',''),'QMT','')

What will be the equivalent function in Hive that will serve the exact same prurpose? Will regexp_replace work for the above scenario?

Used regexp_replace in the above condition instead of replace, but it is throwing me the below error.

FAILED: SemanticException [Error 10014]: Line 1:255 Wrong arguments ''('': No matching method for class org.apache.hadoop.hive.ql.udf.UDFRegExpReplace with (string, string). Possible choices: _FUNC_(string, string, string) (state=42000,code=10014)

It would be greatful if someone can help on this. Thanks

Solution

  • That is true. regexp_replace is the command that you can use. You can give the list of substrings to be substituted as a list separated by '|' and you need to use '\' to give any special characters which might act as wildcards like - . or ? or * etc. Below is the sample usage.

    SELECT regexp_replace(upper(fieldname),'LTD|QMT|INTERNATIONAL|ORG|INC|\\.|,| ','') from my_table;