CODEWITHSUNDEEP
hadoophiveregexp-replace
daniyal.bashir
daniyal.bashir

Reputation: 88

Replacing PIPE (|) symbol in hive

Hello i have a text containing pipe (|) symbol and i want to replace it with space. This is the text in the column description

|TrueCricketLover|M€$$!| PTI|Capricorn|No DM|#TeamIK|@shaneRWatson33 ❤ Boom Boom❤ Striving to be a better human!

I have tried the regexp_replace function like this

regexp_replace(description,'|',' ')

This command returns this value

| T r u e C r i c k e t L o v e r | M € $ $ ! | P T I | C a p r i c o r n | N o D M | # T e a m I K | @ s h a n e R W a t s o n 3 3 ❤ B o o m B o o m ❤ S t r i v i n g t o b e a b e t t e r h u m a n ! L o v e h i m w h o l e a s t D e s e r v e s I t , T h a t ' s i t ❤

It is not replacing the pipe (|) symbol. Kindly help.

Upvotes: 1

Views: 5684

Answers (3)

Amit
Amit

Reputation: 11

Try this one add \ in your regexp_replace function

insert overwrite table_name select regexp_replace(id,'\\|',' ') from table_name

Upvotes: 0

Nick Burns
Nick Burns

Reputation: 192

Since a pipe character is an OR operator in regex in must be escaped. In Java flavored regex, two escape characters, back slashes, must be used.

Upvotes: 3

kundam gouthami
kundam gouthami

Reputation: 89

Try this: select regexp_replace(description,'\\|',' ') from table;

Upvotes: 8

Related Questions