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: [email protected], 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) [email protected] fro this do u want abc alone or xyz.com

"abc"
select substr('[email protected]', 1, instr('[email protected]', '@') -1 ) from dual;

"xyz.com"
select substr('[email protected]', instr('[email protected]', '@') +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:[email protected]) 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('[email protected]', null);
insert into test values('[email protected]', null);
insert into test values('[email protected]', null);

commit;

select * from test;
EMAIL DOMAIN
------------------
[email protected]
[email protected]
[email protected]

update test set domain = substr(email, instr(email, '@') +1 );

3 rows updated.

commit;

select * from test;

EMAIL DOMAIN
---------------------
[email protected] xyz.com
[email protected] bbb.com
[email protected] ccc.com

Cheers!

[rkiss]

Yes, that is it.

Thanks