Extract domain name from email address field [message #533388] |
Tue, 29 November 2011 14:43 |
|
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 #533524 is a reply to message #533393] |
Wed, 30 November 2011 07:34 |
|
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 #533528 is a reply to message #533524] |
Wed, 30 November 2011 07:51 |
John Watson
Messages: 8951 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 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
heidibb wrote on Wed, 30 November 2011 08:34I 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 #533550 is a reply to message #533543] |
Wed, 30 November 2011 08:53 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
heidibb wrote on Wed, 30 November 2011 09:49that 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 #670408 is a reply to message #670386] |
Tue, 03 July 2018 06:15 |
Bill B
Messages: 1971 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
|
|
|