instrb
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: instrb

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    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.

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  3. #3
    Join Date
    Jun 2003
    Posts
    132
    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

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  5. #5
    Join Date
    Jun 2003
    Posts
    132
    Yes, that is it.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width