Bala Bala - 1 year ago 86
SQL Question

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.

Answer Source

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]'       ,'(?<[email protected]).*(?=\\.)',0);
OK
gmail

hive> select  regexp_extract('[email protected]'  ,'(?<[email protected]).*(?=\\.)',0);
OK
ffff.nec.co
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download