using replace function

On Nov 28, 2019, at 1:09 PM, Antonio Silva <aolinto.lst@gmail.com> 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’;

On Thursday, November 28, 2019, Rob Sargent <robjsargent@gmail.com> wrote:

On Nov 28, 2019, at 1:09 PM, Antonio Silva <aolinto.lst@gmail.com>

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.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thursday, November 28, 2019, Rob Sargent <robjsargent@gmail.com> wrote:

I want to replace a string (not a substring) in a field but making sure
that the string in the full field.

I’d probably do something like:
Select case when a.col = ‘value’ then ‘new value’ else a.col end from a;

Yeah, this. You could wrap it up in a SQL function if you want the
same level of notational convenience as replace().

Another possibility is regexp_replace with an anchored pattern, but
that would potentially require escaping regexp metacharacters in the
pattern, so the PITA factor is high. And I doubt it'd be faster than
the CASE solution.

regards, tom lane

Hi Osvaldo, Rod, David and Tom

Thanks for your attention. Lastly it is not that difficult.

For me the more natural way is Rod's suggestion, to use replace with where.
I don't know why I didn't think of this solution.

The usage of regexp_replace is very nice too and I will take a time to
learn it.

Thanks a lot to you all

Antonio

Em sex., 29 de nov. de 2019 às 01:22, Tom Lane <tgl@sss.pgh.pa.us> escreveu:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thursday, November 28, 2019, Rob Sargent <robjsargent@gmail.com>

wrote:

I want to replace a string (not a substring) in a field but making sure
that the string in the full field.

I’d probably do something like:
Select case when a.col = ‘value’ then ‘new value’ else a.col end from a;

Yeah, this. You could wrap it up in a SQL function if you want the
same level of notational convenience as replace().

Another possibility is regexp_replace with an anchored pattern, but
that would potentially require escaping regexp metacharacters in the
pattern, so the PITA factor is high. And I doubt it'd be faster than
the CASE solution.

regards, tom lane

--
Antônio Olinto Ávila da Silva
Biólogo / Oceanógrafo
Instituto de Pesca (Fisheries Institute)
São Paulo, Brasil