Re: replace substring

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
> >
> >
>
>
Received on Tue May 23 2000 - 00:00:00 CDT