Hive: regexp_replace square brackets

Question

I need to replace the square brackets around a key:value pair similar to the following. Any help is much appreciated!

data in 'properties' looks like this:

name: property1
value: [12345667:97764458]

**code**
SELECT p.name, regexp_replace(p.value,'[','') AS value
FROM properties p

Solved: Revised code

SELECT p.name, regexp_replace(p.value,'\\[|\\]','') AS value
FROM properties p;

code: regexp_replace(p.value,'\\[|\\]','') works – dolphinZhang Dec 11 '18 at 06:20 — dolphinZhang, Dec 11 '18 at 06:20

score 4 · Answer 1 · answered Feb 05 '14 at 18:44

You always need to double your backslashes in Hive regexes. That's because a single backslash is used as an escape character in Hive strings so it would get stripped off before the regex parser can see it. A double backslash becomes a single backslash, which is what you need.

To see how backslashes get stripped just run a select using your regex as a string literal:

select '\\[' from t limit 1;
OK
\[

score 2 · Answer 2 · answered Jan 15 '14 at 02:31

2

Here is you regex [\[\]]+ this will match one or more [ and ] in a string.

answered Jan 15 '14 at 02:31

João Pinho

3,725
1
19
29

João, thanks. I'm still getting an error running this. Maybe I'm using the code incorrectly? Please see the revised code in the original submission. – Sarah Bergquist Jan 15 '14 at 03:23
pe.value does not exist, you table name alias is 'p'. – João Pinho Jan 15 '14 at 13:08
Sorry that was a typo in the code above. I added the error returned. – Sarah Bergquist Jan 15 '14 at 16:29

Hive: regexp_replace square brackets

2 Answers2