Re: Replace part of a text string using sql

Hows this for doing your work for you.

try
update user set email = replace (email, 'com', 'co.uk') where username like 'J%';

or this one which replaces everything after the @

update user set email =
substr(email, 1, instr(email, '@') - 1) || REPLACE(substr(email, instr(email, '@'), 100), '.com', '.co.uk') where
username like 'J%';

John Guthrie wrote:

> Hi All,
>
> I am trying to update part of a text string in a Varchar2 Column.
>
> replace:
> somebody_at_domainname.com
> with
> somebody_at_domainname.co.uk'
>
> I tried using the following sql:
>
> update user set email = replace ('domainname.com', 'com', 'co.uk') where
> username like 'J%';
>
> This produced the following result, it chopped of the "somebody" part:
> domainname.co.uk
>
> instead of returning
> somebody_at_domainname.co.uk
>
> Any ideas?
> Thanks for the help!
>
> Regards,
> John Guthrie

--
Suzuki SV650S - plop.  Gone.
Kwak ZX-6R J2 - hear the roar