CODEWITHSUNDEEP
sqlregexhivehiveql
Bala
Bala

Reputation: 67

Extract domain_name alone from email_id in Hive

In my Hive table, i have an email address column : [email protected] i want to display "gmail" alone. I tried using regexp_extract but not getting the desired output. nowhere near.

Also for email ids like [email protected] , i want the output to be "ffff.nec.co"

Below is my expression : select regexp_extract('[email protected]','.([^.]+)') but it does not bring the desired output. Please help me out guys. Regexp or substr is fine for me.

Upvotes: 2

Views: 2517

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Option 1

hive> select  regexp_extract('[email protected]'       ,'@(.*)\\.',1);
OK
gmail

hive> select  regexp_extract('[email protected]'  ,'@(.*)\\.',1);
OK
ffff.nec.co

Option 2

hive> select  regexp_extract('[email protected]'       ,'(?<=@).*(?=\\.)',0);
OK
gmail

hive> select  regexp_extract('[email protected]'  ,'(?<=@).*(?=\\.)',0);
OK
ffff.nec.co

Upvotes: 1

Related Questions