Home > mailing lists

Thread: using replace function

using replace function

From

Antonio Silva

Date:

29 November 2019, 00:09:14

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

Re: using replace function

From

Rob Sargent

Date:

29 November 2019, 01:48:07


> 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’;

Re: using replace function

From

"David G. Johnston"

Date:

29 November 2019, 04:54:38

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.

Re: using replace function

From

Tom Lane

Date:

29 November 2019, 07:22:24

"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

Re: using replace function

From

Antonio Silva

Date:

02 December 2019, 19:26:26

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