Home » SQL & PL/SQL » SQL & PL/SQL » Extract domain name from email address field (PS/SQL)
Extract domain name from email address field [message #533388] Tue, 29 November 2011 14:43 Go to next message
heidibb
Messages: 19
Registered: November 2011
Junior Member
Hello,

I have a field for email address.
I would like to create a field to capture just the domain (after the @ and before the .com) so I can process a count of records by domain.

I'm wondering the best way to go about this.
Re: Extract domain name from email address field [message #533390 is a reply to message #533388] Tue, 29 November 2011 14:46 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


use INSTR() & SUBSTR() functions
Re: Extract domain name from email address field [message #533393 is a reply to message #533390] Tue, 29 November 2011 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or, if you like regular expressions;
select regexp_replace('<your email','^.*@([^\.]+)\.com','\1') from dual;


Regards
Michel
Re: Extract domain name from email address field [message #533524 is a reply to message #533393] Wed, 30 November 2011 07:34 Go to previous messageGo to next message
heidibb
Messages: 19
Registered: November 2011
Junior Member
I have used the instr and substr functions and think I'm really close since the select is doing what I want, but when I go to update the table, I'm getting an error -- ORA-01427 - Single row subquery returns more than one row.

alter table t4593w2_dropped add DOMAIN varchar2(30)

update t4593w2_dropped 
set domain = 
      (select (substr (email_address, instr (email_address,'@',1)+1 ))
       from t4593w2_dropped)


[Updated on: Wed, 30 November 2011 07:45] by Moderator

Report message to a moderator

Re: Extract domain name from email address field [message #533526 is a reply to message #533524] Wed, 30 November 2011 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not satify your requirements: "after the @ and before the .com".

Regards
Michel
Re: Extract domain name from email address field [message #533528 is a reply to message #533524] Wed, 30 November 2011 07:51 Go to previous messageGo to next message
John Watson
Messages: 8458
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I would like to create a field to capture just the domain
(after the @ and before the .com) so I can process a count of records by domain.

You don't need a new column to get this result. Try this:
select substr(email_address, instr (email_address,'@',1)+1 ),count(*)
from employees
group by substr(email_address, instr (email_address,'@',1)+1 );

(I can't test, because you didn;t give the CREATE TABLE and INSERT statements)
Re: Extract domain name from email address field [message #533529 is a reply to message #533524] Wed, 30 November 2011 07:52 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
heidibb wrote on Wed, 30 November 2011 08:34
I have used the instr and substr functions and think I'm really close since the select is doing what I want, but when I go to update the table, I'm getting an error -- ORA-01427 - Single row subquery returns more than one row.


Error message is quite clear. Your subquery returns more than one row. Which one is it supposed to use to update the row? There is no correlation between the update and the subquery.
Why is the subquery there in the first place?
Re: Extract domain name from email address field [message #533530 is a reply to message #533526] Wed, 30 November 2011 07:53 Go to previous messageGo to next message
heidibb
Messages: 19
Registered: November 2011
Junior Member
You are right. I modified my original requirements to include the .com since my ultimate goal is to get a count by each domain... just in case there are instances of records that have xxx.com or xxx.net or xxx.org, etc. -- I want to be sure and count correctly.
Re: Extract domain name from email address field [message #533532 is a reply to message #533530] Wed, 30 November 2011 07:58 Go to previous messageGo to next message
heidibb
Messages: 19
Registered: November 2011
Junior Member
I want to use the email_address field to update the new 'domain' field for each record, populating the 'domain' field with everything after the '@' from the 'email_address' field.

For example, I have first, last, email_address
I modified the table to include a domain field, so now I have first, last, email_address, domain

I would like the records to be like this

first    last      email_address          domain
heidi    barnes    hbb@roadrunner.com     roadrunner.com
Re: Extract domain name from email address field [message #533533 is a reply to message #533530] Wed, 30 November 2011 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Other expressions to get it are:
regexp_replace(email_address,'^.*@')
regexp_substr(email_address,'[^@]*$')

Regards
Michel

[Updated on: Wed, 30 November 2011 08:00]

Report message to a moderator

Re: Extract domain name from email address field [message #533537 is a reply to message #533533] Wed, 30 November 2011 08:07 Go to previous messageGo to next message
heidibb
Messages: 19
Registered: November 2011
Junior Member
Thanks Michel! those are also working, but I'm still stumped by how to update the new 'domain' field in my table by using one of these statements.
Be gentle... I'm new to SQL Smile

I currently have first, last, email_address in my table.
I added 'domain' to the table and am trying to use the contents of the email_address field to populate the new 'domain' field for each record.

Ultimately, I would like the records in the table to be like this

first    last      email_address          domain
heidi    barnes    hbb@roadrunner.com     roadrunner.com
Re: Extract domain name from email address field [message #533539 is a reply to message #533537] Wed, 30 November 2011 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
update ... set domain = <any of the above expressions>

Regards
Michel

[Updated on: Wed, 30 November 2011 08:21]

Report message to a moderator

Re: Extract domain name from email address field [message #533543 is a reply to message #533539] Wed, 30 November 2011 08:49 Go to previous messageGo to next message
heidibb
Messages: 19
Registered: November 2011
Junior Member
that is exactly what I'm doing and still getting the error ORA-01427 - Single row subquery returns more than one row.


update t4593w2_dropped 
       set domain = (select (substr (email_address, instr (email_address,'@',1)+1 ))from t4593w2_dropped)


also happening with this

update t4593w2_dropped 
       set domain = (select regexp_replace (email_address,'^.*@')from t4593w2_dropped)
Re: Extract domain name from email address field [message #533546 is a reply to message #533543] Wed, 30 November 2011 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
that is exactly what I'm doing

No this is what I posted.
Do you see any SELECT in the expressions I posted below "Other expressions to get it are"?

Regards
Michel
Re: Extract domain name from email address field [message #533549 is a reply to message #533543] Wed, 30 November 2011 08:52 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
"domain" is a scalar variable which means it can only accept a single value
Therefore the SELECT statement must return only a single value; so an appropriate WHERE clause must exist to return 1 row.
Re: Extract domain name from email address field [message #533550 is a reply to message #533543] Wed, 30 November 2011 08:53 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
heidibb wrote on Wed, 30 November 2011 09:49
that is exactly what I'm doing and still getting the error ORA-01427 - Single row subquery returns more than one row.


update t4593w2_dropped 
       set domain = (select (substr (email_address, instr (email_address,'@',1)+1 ))from t4593w2_dropped)


also happening with this

update t4593w2_dropped 
       set domain = (select regexp_replace (email_address,'^.*@')from t4593w2_dropped)


No, no no. You do NOT need a subquery here. Just follow the template that Michel posted right before your message.

This is a random update statement
update emp set salary = salary * 1.1;

No subquery. Straight update.

[Updated on: Wed, 30 November 2011 08:53]

Report message to a moderator

Re: Extract domain name from email address field [message #533551 is a reply to message #533550] Wed, 30 November 2011 09:08 Go to previous messageGo to next message
heidibb
Messages: 19
Registered: November 2011
Junior Member
Thanks everyone.

This did work - I was over thinking it. I appreciate your help with this!!

update t4593w2_dropped 
       set domain = regexp_replace (email_address,'^.*@')
Re: Extract domain name from email address field [message #533566 is a reply to message #533551] Wed, 30 November 2011 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback and solution.
Note that you could also use your own expression with SUBSTR and INSTR. Smile

Regards
Michel
Re: Extract domain name from email address field [message #670386 is a reply to message #533528] Sun, 01 July 2018 08:55 Go to previous messageGo to next message
kenganapawankumar
Messages: 1
Registered: July 2018
Junior Member
Thank you so much bro it's working and it's helps me a lot
Re: Extract domain name from email address field [message #670408 is a reply to message #670386] Tue, 03 July 2018 06:15 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
The following code uses instr and is faster then the regular expression functions. It will also search from the end of the email_address string stopping on the first '@'. Also if you are counting domains dont forget to either store them in the same case (lower,upper) or force them to the same case when you query for the count.

update t4593w2_dropped set domain = substr(email_address,instr(email_address,'@',-1)+1); 

[Updated on: Tue, 03 July 2018 06:22]

Report message to a moderator

Previous Topic: Select using DBMS_SQL.EXECUTE (merged)
Next Topic: How to get decimal value from string and convert
Goto Forum:
  


Current Time: Sun Jan 24 00:56:10 CST 2021