I have the value of 58.3308% in the table(include % character), I need output of 0.583308, and the SQL query as below works fine in SQL console
select to_char((rtrim('58.3308%', '%') /100), '0.999999') from dual;
But I get this error in my python code, it’s a hive/impala problem, right?
AnalysisException: No matching function with signature: rtrim(VARCHAR(40), STRING)
Thanks so much for any advice
Advertisement
Answer
As per Hive manual, rtrim only removes whitespaces and thus does not take a second parameter. If you want to remove other characters, consider using regexp_replace as in regexp_replace('58.3308%', '%', '')
As per documentation:
rtrim(string A)
Returns the string resulting from trimming spaces from the end(right hand side) of A. For example, rtrim(‘ foobar ‘) results in ‘ foobar’.
and
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace(“foobar”, “oo|ar”, “”) returns ‘fb.’ Note that some care is necessary in using predefined character classes: using ‘s’ as the second argument will match the letter s; ‘s’ is necessary to match whitespace, etc.
By the way, the result of this operation will be a string, so you should cast it to float just to be safe.