[FIXED] hive replace forward slash/backward slash with tripple slashes

Issue

I have a table that has / or \ as first value and I wanted to replace it with 3 backward slashes \\\. Tried many ways including concatenation but nothing worked so far. Any help is appreciated.

create table test ( s1 string )

insert into test(s1) values ('/')
insert into test(s1) values ('ab')
insert into test(s1) values ('\\')

select s1, regexp_replace(s1, '//','\\\\\\\\\\\\\\') from test;

Solution

This should work:

regexp_replace(s1,'[/\\\\]', '\\\\\\\\\\\\')

Regex [/\\\\] - means slash or backslash

It will replace all occurrences of slash or backslash with 3 backslashes. If you want to match exactly string containing only slash or backslash, use anchors: ^[/\\\\]$

Also in the regexp you can use hex code '\\x5c' to match backslash: '[/\\x5c]'

Answered By - leftjoin
Answer Checked By - David Goodson (JavaFixing Volunteer)