Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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'.
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.
                Thank you so much for your advice, use this cast(regexp_replace('58.3308%', '%%', '') as decimal(20,8))*0.01, it works fine now, 🌹☕️🍺
– user12336707
                Nov 19, 2019 at 8:23
                Hi, thanks so much for the answer, I got an error of AnalysisException: Syntax error in line 2: ...e, split('58.3308%', '%')[0]/100 as val                              ^ Encountered: [ Expected: AND, AS, ASC, BETWEEN, BLOCK_SIZE, COMMENT, COMPRESSION, CROSS, DEFAULT, DESC, DIV, ELSE, ENCODING, END, FOLLOWING, FROM, FULL, GROUP, IGNORE, HAVING, ILIKE, IN, INNER, IREGEXP, IS, JOIN.............  CAUSED BY: Exception: Syntax error  unable to rollback
– user12336707
                Nov 19, 2019 at 8:20
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.