using replace function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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
lup
Reply | Threaded
Open this post in threaded view
|

Re: using replace function

lup


> On Nov 28, 2019, at 1:09 PM, Antonio Silva <[hidden email]> 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’;



Reply | Threaded
Open this post in threaded view
|

Re: using replace function

David G Johnston
On Thursday, November 28, 2019, Rob Sargent <[hidden email]> wrote:


> On Nov 28, 2019, at 1:09 PM, Antonio Silva <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: using replace function

Tom Lane-2
"David G. Johnston" <[hidden email]> writes:
> On Thursday, November 28, 2019, Rob Sargent <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> escreveu:
"David G. Johnston" <[hidden email]> writes:
> On Thursday, November 28, 2019, Rob Sargent <[hidden email]> 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