-
instrb
Hello,
I have a table with an email attribute. I have added a new column, called "domain". I would like to insert the domain name for each row by taking the end part of the email text (example: jdoe@abc.com, then the domain would be abc.com).
Can someone please help me with the code?
Thank you.
-
wht do u actually want???
can u give us some example?
(eg) abc@xyz.com fro this do u want abc alone or xyz.com
"abc"
select substr('abc@xyz.com', 1, instr('abc@xyz.com', '@') -1 ) from dual;
"xyz.com"
select substr('abc@xyz.com', instr('abc@xyz.com', '@') +1 ) from dual;
Cheers!
Cheers!
OraKid.
-
I would like the sql statement to go through each row in my table (20,000 records) and take the email value (example:jdoe@abc.com) and insert the domain part of the email (abc.com) into domain column FOR EACH ROW.
example:
1. for each row select the domain part of the email value.
2. then update the domain column with the the results of step 1.
Thanks
-
is this wht u want....
create table test(email varchar2(100), domain varchar2(100));
insert into test values('abc@aaa.com', null);
insert into test values('def@bbb.com', null);
insert into test values('ghi@ccc.com', null);
commit;
select * from test;
EMAIL DOMAIN
------------------
abc@xyz.com
def@bbb.com
ghi@ccc.com
update test set domain = substr(email, instr(email, '@') +1 );
3 rows updated.
commit;
select * from test;
EMAIL DOMAIN
---------------------
abc@xyz.com xyz.com
def@bbb.com bbb.com
ghi@ccc.com ccc.com
Cheers!
Cheers!
OraKid.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|