Postgres Pro
Downloads
Home   >   mailing lists

Thread: replace function

replace function

From
rute solipa
Date:
hi everyone,

i've got an oracle query that goes like this:
select  replace (v.ds_conf, 'ns', 'p/fazer') as conf.
from tb_visitas

the result of this query is everytime is find string 'ns' it will be 
replaced by 'p/fazer'.

does any one can tell me how do i do this in postgresql.

kind regards,

rute

Re: replace function

From
rute solipa
Date:
Rajesh Kumar Mallah wrote:<br /><blockquote cite="midPine.LNX.4.33.0304282036300.645-100000@localhost.localdomain"
type="cite"><prewrap="">On Mon, 28 Apr 2003, rute solipa wrote:
 
 </pre><blockquote type="cite"><pre wrap="">hi everyone,

i've got an oracle query that goes like this:
select  replace (v.ds_conf, 'ns', 'p/fazer') as conf.
from tb_visitas

the result of this query is everytime is find string 'ns' it will be 
replaced by 'p/fazer'.   </pre></blockquote><pre wrap="">


It works exactly the same way here :)
in postgresql 7.3.x it is inbuilt , in older
version it can be installed from the postgresql
cookbook website ,


Regds
mallah.

 </pre><blockquote type="cite"><pre wrap="">does any one can tell me how do i do this in postgresql.

kind regards,

rute


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
   </pre></blockquote><pre wrap="">
 </pre></blockquote> but i have the 7.2.2 version<br />

Re: replace function

From
Rajesh Kumar Mallah
Date:
On Mon, 28 Apr 2003, rute solipa wrote:

> hi everyone,
> 
> i've got an oracle query that goes like this:
> select  replace (v.ds_conf, 'ns', 'p/fazer') as conf.
> from tb_visitas
> 
> the result of this query is everytime is find string 'ns' it will be 
> replaced by 'p/fazer'.



It works exactly the same way here :)
in postgresql 7.3.x it is inbuilt , in older
version it can be installed from the postgresql
cookbook website ,


Regds
mallah.


> 
> does any one can tell me how do i do this in postgresql.
> 
> kind regards,
> 
> rute
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: replace function

From
"Victor Yegorov"
Date:
* rute solipa <rutes@eselx.ipl.pt> [28.04.2003 18:08]:
> hi everyone,
>
> i've got an oracle query that goes like this:
> select  replace (v.ds_conf, 'ns', 'p/fazer') as conf.
> from tb_visitas
>
> the result of this query is everytime is find string 'ns' it will be
> replaced by 'p/fazer'.
>
> does any one can tell me how do i do this in postgresql.
>
> kind regards,

replace(string text, from text, to text)
See `String functions' in the PostgreSQL manual.

--

Victor Yegorov

Re: replace function

From
Rajesh Kumar Mallah
Date:

you have to install the function then.

In 7.2.2 
if not installed plpgsql already install
it by issuing.

you could use pltcl too.

for plpgsql below is the procedure:

$ createlang plpgsql <dbname>  

$ save the text below

taken from 
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23


--  BEGINS HERE ---
create function replace (varchar, varchar, varchar) returns varchar as '
declare   string alias for $1;   sub alias for $2;   replacement alias for $3;   -- xxxxxxxxxxx[MATCH]xxxxxxxxxxxx   --
         | end_before   --                   | start_after   match integer;   end_before integer;   start_after
integer;  string_replaced varchar;   string_remainder varchar;
 
begin   string_remainder := string;   string_replaced := '''';   match := position(sub in string_remainder);
   while match > 0 loop       end_before := match - 1;       start_after := match + length(sub);       string_replaced
:=string_replaced || substr(string_remainder, 1, 
 
end_b
efore) || replacement;       string_remainder := substr(string_remainder, start_after);       match := position(sub in
string_remainder);  end loop;   string_replaced := string_replaced || string_remainder;
 
   return string_replaced;
end;
' LANGUAGE 'plpgsql';

-- ENDS HERE --------

in a file named replace.sql

install it in your database by

$ psql -Uusername dbname -f replace.sql

then use the normal syntax from psql



BTW You shud upgrade to latest 7.3.2 or 
7.2.4 if u have any compelling reasons
to stay in 7.2.x versions.

Regds
mallah.



On Mon, 28 Apr 2003, rute solipa wrote:

> Rajesh Kumar Mallah wrote:
> 
> >On Mon, 28 Apr 2003, rute solipa wrote:
> >
> >  
> >
> >>hi everyone,
> >>
> >>i've got an oracle query that goes like this:
> >>select  replace (v.ds_conf, 'ns', 'p/fazer') as conf.
> >>from tb_visitas
> >>
> >>the result of this query is everytime is find string 'ns' it will be 
> >>replaced by 'p/fazer'.
> >>    
> >>
> >
> >
> >
> >It works exactly the same way here :)
> >in postgresql 7.3.x it is inbuilt , in older
> >version it can be installed from the postgresql
> >cookbook website ,
> >
> >
> >Regds
> >mallah.
> >
> >
> >  
> >
> >>does any one can tell me how do i do this in postgresql.
> >>
> >>kind regards,
> >>
> >>rute
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 4: Don't 'kill -9' the postmaster
> >>
> >>    
> >>
> >
> >
> >  
> >
> but i have the 7.2.2 version
>