Skip to content
Advertisement

AnalysisException: No matching function with signature: rtrim(VARCHAR(40), STRING)

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement