Обсуждение: using replace function

Поиск
Список
Период
Сортировка

using replace function

От
Antonio Silva
Дата:
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

От
Rob Sargent
Дата:

> 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

От
"David G. Johnston"
Дата:
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

От
Tom Lane
Дата:
"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

От
Antonio Silva
Дата:
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