Re: replace substring

Svein Fossdal wrote:
>
> Thanx for your answer - this seems to be the function I was looking for.
>
> But how do you use it on columns in a table?
> For example, if we have the table TEST with a column TEXT and
> we want to replace all occurences of a substring of TEXT in all rows.
> I tried
> select replace(select tekst from test,'e','E') from TEST;
>
> but it seems to want a pure text string as an input.
>
> How do I use this function to change every occurence of a
> specific substring to a new one in a column of a table, for
> all rows?
>
> Thanx in advance.
>
> Regards,
>
> Svein Fossdal
>
> Michel Cadot <micadot_at_netcourrier.com> wrote in message
> news:8ge4hu$40p$1_at_news2.isdnet.net...
> > What about the replace function of SQL?
> >
> > REPLACE
> > Syntax REPLACE(char, search_string[,replacement_string])
> >
> > Purpose
> >
> > Returns char with every occurrence of search_string replaced
> > with replacement_string. If replacement_string is omitted or
> > null, all occurrences of search_string are removed. If
> > search_string is null, char is returned. This function provides
> > a superset of the functionality provided by the TRANSLATE
> > function. TRANSLATE provides single character, one to one,
> > substitution. REPLACE allows you to substitute one string
> > for another as well as to remove character strings.
> >
> > Example
> >
> > SELECT REPLACE('JACK and JUE','J','BL') "Changes"
> > FROM DUAL
> >
> > Changes
> > --------------
> > BLACK and BLUE
> >
> > --
> > Have a nice day
> > Michel
> >
> >
> > Svein Fossdal <sveifo_at_frisurf.no> a écrit dans le message :
 mfwW4.10645$C9.190172_at_news1.online.no...
> > > Hello
> > >
> > > Does anybody have a good function or procedure that
> > > finds every occurence of a given substring and changes
> > > this to a new one?
> > > A typical function call would be something like
> > > search_and_replace('old_substring', 'new_substring', column, table)
> > >
> > > I am very greatful for any help.
> > >
> > > Regards
> > > Svein Fossdal
> > >
> > >
> >
> >

Try:

  update mytable set myCol = replace(myCol,'oldSubStr','newSubStr');

Yours,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Tue May 23 2000 - 00:00:00 CDT