Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: update email address

Re: update email address

From: Mark Bole <makbo_at_pacbell.net>
Date: Tue, 05 Apr 2005 22:56:06 GMT
Message-ID: <aIE4e.17801$zl.3582@newssvr13.news.prodigy.com>


Sheela wrote:
> Hi there,
> I would like to update 5000 email address from
>
> name_at_xxxxxx.com to name_at_yyyyy.com.
> name1_at_xxxxxx.com to name1_at_yyyyy.com
> -----
> ------
> -----
> I tried this statment :
> update student a set
> substr(a.email_addr,(instr(a.email_addr,'@')+1),10) = 'yyyyy.com'where
> stud_id like 'name'
>
>
> but I am getting ORA-00927: missing equal sign
>

Malcolm Dew-Jones wrote:

> HansF (News.Hans_at_telus.net) wrote:
> : On Tue, 05 Apr 2005 13:55:02 -0700, Sheela interested us by writing:
> 
> : > update student a set
> : > substr(a.email_addr,(instr(a.email_addr,'@')+1),10) = 'yyyyy.com'where
> 
> : Basically the SET clause requires a column on the left side immediately
> : after the 'set', but you seem to have a wild expression in place of the
> : column name.  (Nor does your statement meet any of the other variations
> : permitted.)
> 
> It looks like valid perl to update a portion of a string.  I suspect the 
> poster has "losing track of multiple contexts" syndrome.
> 

Of course in Perl you would be more likely to do a simple search and replace, but I assume you mean, Perl's use of a function such as substr() as an lvalue (something you can assign to).

For the OP: if using version 10g, you might try this: (no error checking for malformed e-mail addresses in this example):

update student
set EMAIL_ADDR = regexp_substr(EMAIL_ADDR, '.*@') || 'yyyyy.com';

BTW, I found another documentation bug: ORA-00927 is not included in version 10g on-line docs.

http://otn.oracle.com/pls/db10g/db10g.error_search?search=00927

-Mark Bole Received on Tue Apr 05 2005 - 17:56:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US