Search code examples
regexhadoophiveregexp-replace

Remove braces and dollar sign from the data using hive regexp_replace

I need to remove dollar-sign and braces from one of the fields in hive.

Sample data:
$210.53
$210.53
($390.53)
($210.53)

The issue is some records have braces and others don't. Below is the syntax I came up with:
select REGEXP_REPLACE(amount, '\(\$|\)','') as amount from table where id=1234;
Output:
$210.53
$210.53
390.53
210.53

This syntax doesn't remove the dollar signs from the records that don't have braces. Can someone guide me on this?

Solution

  • I got it to work: select REGEXP_REPLACE(amount, '(|\$|)|','') as amount from table where id=1234;