instrb

[rkiss]

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.

[balajiyes]

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!

[rkiss]

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

[balajiyes]

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!

[rkiss]

Yes, that is it.

Thanks