Hive regexp_replace failed to replace backslash

Question

I have a table with a single column name_string, which contains backslash character. I wanted to remove the backslash character using regexp_replace, but it does not work.

Table:

create table t (name_string varchar(100));
insert into table t values ('\\"aaa\\"'), ('\\"bbb\\"'); 

Query:

select 
   name_string, regexp_replace(name_string, '\\"', '"')
from  t; 

returning

+--------------+----------+
| name_string  |   _c1    |
+--------------+----------+
| \"aaa\"      | \"aaa\"  |
| \"bbb\"      | \"bbb\"  |
+--------------+----------+

However, select regexp_replace('\"aaa\"', '\\"', '"') returns the correct result.

I am confused about why this may be the case. Could someone please shed light on this? Appreciate it!

Answer 1

Use 4 backslashes:

select regexp_replace(name_string,'\\\\"','"') from t; 

Only backslash needs escaping. In Java and in regex the backslash has special meaning and needs escaping.