Re: using replace function

On Thursday, November 28, 2019, Rob Sargent <[email protected]> wrote:

>
>
> > On Nov 28, 2019, at 1:09 PM, Antonio Silva <[email protected]>
> wrote:
> >
> > Hello list
> >
> > I want to replace a string (not a substring) in a field but making sure
> that the string in the full field.
> >
> > In the example below since 'blue' is not the full text in 'blue shark'
> there should not have a replacement.
> >
> > How to say "replace only with 'blue' if it is the full string in the
> field".
> >
> > With REPLACE I get:
> >
> > SELECT REPLACE('blue shark','blue','blue fish');
> >      replace
> > -----------------
> >  blue fish shark
> > (1 row)
> >
> > but I would like to get:
> >
> > SELECT REPLACE('blue shark','blue','blue fish');
> >   replace
> > ------------
> >  blue shark
> > (1 row)
> >
> > Thanks a lot
> >
> > Antonio Olinto
>
> does this help?
> select replace (a.col, ‘value’, ’new value’) where a.col = ‘value’;
>
>
I’d probably do something like:

Select case when a.col = ‘value’ then ‘new value’ else a.col end from a;

I suspect adding where a.col = ‘value’ isn’t viable.  If it is then:

Select ‘new value’ from a where a.col = ‘value’;

David J.