How to remove special chars form string in hive

Question

Hi guys i m completely new to hive ..

1)i am trying to remove dollar sign from amount but its not working am using reg-ex extract.

2)and also want count of amount between 0- 99,100-199 and so on.

Solution

Since your cell has strings of format ($ddd.dd).. I would simply replace $ with ''

EX:

SELECT REGEXP_REPLACE(OrderAmount, '\\$', '') 

For counting between range of 100 you can do the following:

SELECT COUNT(*) FROM order GROUP BY floor(float(REGEXP_REPLACE(OrderAmount, '\\$', ''))/100);