CODEWITHSUNDEEP
hive
Padmanabhan Vijendran
Padmanabhan Vijendran

Reputation: 316

Locate, regexp_replace UDFs Hive to replace string in a column

I have a table like below

names    age  gender
v_mark   25   male
j_david  26   male
S_Rose   28   Female
mark     21   male

First I need to findout the name values contains "underscore" or not, Then I need to replace "underscore" with "space" or "comma" the name values based on the specific condition.

If gender is male, I need to replace "underscore" with space If gender is female, I need to replace "underscore" with comma.

I did search in google, and I found that I need use Locate (to check the "underscore" is there in the value) and regexp_replace (To replace the underscore with comma based on condition) I tried When and Case but getting error. Below is my code.

CASE
(
WHEN LOCATE("_",Table1.names)-1 > 0 AND Table1.gender='male'  THEN regexp_replace(T1.names, "_"," ")
WHEN LOCATE("_",Table1.names)-1 > 0 AND Table1.gender='Female'  THEN regexp_replace(T1.names, "_",",")
ELSE Table1.names
END
    ) AS names1

I need the output like 

names1    age  gender
v mark   25   male
j david  26   male
S,Rose   28   Female
mark     21   male

Can anyone help me.

Upvotes: 0

Views: 777

Answers (2)

sandeep rawat
sandeep rawat

Reputation: 4957

issue is with '(' after case use below code

CASE WHEN LOCATE("_",Table1.names)-1 > 0 AND Table1.gender='male' THEN regexp_replace(T1.names, ""," ") WHEN LOCATE("",Table1.names)-1 > 0 AND Table1.gender='Female' THEN regexp_replace(T1.names, "_",",") ELSE Table1.names END AS names1

Upvotes: 1

ninja123
ninja123

Reputation: 200

Case statements and Regex will give you the desired results. 

SELECT 
  CASE gender
    WHEN 'male' THEN regexp_replace(names, "_"," ")
    WHEN 'Female' THEN regexp_replace(names, "_",",")
  END AS names, 
  age, 
  gender
FROM table;

Upvotes: 0

Related Questions