pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreplace function
10+ messages / 7 participants
[nested] [flat]
* replace function
@ 2003-04-28 15:02 rute solipa <rutes@eselx.ipl.pt>
2003-04-28 15:08 ` Re: replace function Rajesh Kumar Mallah <rmallah@trade-india.com>
2003-04-28 15:13 ` Re: replace function Victor Yegorov <viy@nordlb.lv>
0 siblings, 2 replies; 10+ messages in thread
From: rute solipa @ 2003-04-28 15:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: replace function
2003-04-28 15:02 replace function rute solipa <rutes@eselx.ipl.pt>
@ 2003-04-28 15:08 ` Rajesh Kumar Mallah <rmallah@trade-india.com>
2003-04-28 15:12 ` Re: replace function rute solipa <rutes@eselx.ipl.pt>
1 sibling, 1 reply; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-28 15:08 UTC (permalink / raw)
To: rute solipa <rutes@eselx.ipl.pt>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: replace function
2003-04-28 15:02 replace function rute solipa <rutes@eselx.ipl.pt>
2003-04-28 15:08 ` Re: replace function Rajesh Kumar Mallah <rmallah@trade-india.com>
@ 2003-04-28 15:12 ` rute solipa <rutes@eselx.ipl.pt>
2003-04-28 15:45 ` Re: replace function Rajesh Kumar Mallah <rmallah@trade-india.com>
0 siblings, 1 reply; 10+ messages in thread
From: rute solipa @ 2003-04-28 15:12 UTC (permalink / raw)
To: Rajesh Kumar Mallah <rmallah@trade-india.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: replace function
2003-04-28 15:02 replace function rute solipa <rutes@eselx.ipl.pt>
2003-04-28 15:08 ` Re: replace function Rajesh Kumar Mallah <rmallah@trade-india.com>
2003-04-28 15:12 ` Re: replace function rute solipa <rutes@eselx.ipl.pt>
@ 2003-04-28 15:45 ` Rajesh Kumar Mallah <rmallah@trade-india.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-28 15:45 UTC (permalink / raw)
To: rute solipa <rutes@eselx.ipl.pt>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: replace function
2003-04-28 15:02 replace function rute solipa <rutes@eselx.ipl.pt>
@ 2003-04-28 15:13 ` Victor Yegorov <viy@nordlb.lv>
1 sibling, 0 replies; 10+ messages in thread
From: Victor Yegorov @ 2003-04-28 15:13 UTC (permalink / raw)
To: rute solipa <rutes@eselx.ipl.pt>; +Cc: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Replace function ...
@ 2004-05-17 14:58 Kornelije <krabuzin@foi.hr>
2004-05-17 19:34 ` Re: Replace function ... Yasir Malik <ymalik@cs.stevens-tech.edu>
2004-05-19 10:24 ` Re: Replace function ... Benoît BOURNON <benoit.bournon@adelis.com>
2004-05-19 17:31 ` Re: Replace function ... Kornelije Rabuzin <krabuzin@foi.hr>
0 siblings, 3 replies; 10+ messages in thread
From: Kornelije @ 2004-05-17 14:58 UTC (permalink / raw)
To: pgsql-sql
Hi,
I need to write a function which has to replace a character with two or three other characters ....
I have some data in the DB and when I retrieve this data, I have sorting problems because I use specific Croatian characters ....
I know that the DB is not setup properly, but I cannot change anything now because some data could be lost ....
So I need to change specific Croatian characters with some other standard characters in order to sort the data properly ....
so, if anybody knows something or has such function, please, let me know ....
thanks
R.K.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replace function ...
2004-05-17 14:58 Replace function ... Kornelije <krabuzin@foi.hr>
@ 2004-05-17 19:34 ` Yasir Malik <ymalik@cs.stevens-tech.edu>
2 siblings, 0 replies; 10+ messages in thread
From: Yasir Malik @ 2004-05-17 19:34 UTC (permalink / raw)
To: Kornelije <krabuzin@foi.hr>; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I need to write a function which has to replace a character with two or three other characters ....
>
You can use the replace function as specified below:
replace(string text, from text, to text) text
Replace all occurrences in @string of substring @from with substring @to.
replace( 'abcdefabcdef', 'cd', 'XX') ==> abXXefabXXef
Yasir
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (NetBSD)
iQEVAwUBQKkT1+J7vYSSIbWdAQL/5QgAmCdJrYh4YjJFWKeWNk+OEuhpvRNYbRlM
tH5dezmUrR4fkM4l2mfrmuIkTT+rqQNyRR3E5PhKyfkbnHdw7/WPalqGSbX1aIfP
YZI/RdxH/Aqy+hO+zcUQzSjpYSEyKrmTVGLmRf+tyYg0QdZdAQFd6O9aKWMeCo28
TFKRFAlg+glT/YiN1JiwzF/QygBNmo3g+JDWZ+U8Au+y0fHh/3KeoyLx9ipMSqs/
uBiy8eNjOdAF3ihoydDw2uBpzjwtGv9eeR0Myh8m4Zvx9K239NVSz4s9a+2R1Onf
Mr5PK7Te5TG8TLcjJyo35x8yC/57oufNvWoi+Q6Y7cmSxeiEcHzJiw==
=xChX
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replace function ...
2004-05-17 14:58 Replace function ... Kornelije <krabuzin@foi.hr>
@ 2004-05-19 10:24 ` Benoît BOURNON <benoit.bournon@adelis.com>
2004-05-19 17:30 ` Re: Replace function ... Kornelije Rabuzin <krabuzin@foi.hr>
2 siblings, 1 reply; 10+ messages in thread
From: Benoît BOURNON @ 2004-05-19 10:24 UTC (permalink / raw)
To: Kornelije <krabuzin@foi.hr>; +Cc: pgsql-sql
I try to reencode postgresql db ... all is running ... in your shell :
- pg_dump db -f fileout
- iconv fileout i do not remember parameters but iconv converts the file.
- createdb db -U unicode (for example)
- pgsql db < fileout_converted
We change previous year the encoding of this website : www.ape-europe.org
'iso' to 'utf8' for problems of eastern countries ...
all is running ...
maybe you can try ...
Kornelije wrote:
> Hi,
>
> I need to write a function which has to replace a character with two
> or three other characters ....
>
> I have some data in the DB and when I retrieve this data, I have
> sorting problems because I use specific Croatian characters ....
> I know that the DB is not setup properly, but I cannot change anything
> now because some data could be lost ....
>
> So I need to change specific Croatian characters with some other
> standard characters in order to sort the data properly ....
>
> so, if anybody knows something or has such function, please, let me
> know ....
>
> thanks
>
> R.K.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replace function ...
2004-05-17 14:58 Replace function ... Kornelije <krabuzin@foi.hr>
2004-05-19 10:24 ` Re: Replace function ... Benoît BOURNON <benoit.bournon@adelis.com>
@ 2004-05-19 17:30 ` Kornelije Rabuzin <krabuzin@foi.hr>
0 siblings, 0 replies; 10+ messages in thread
From: Kornelije Rabuzin @ 2004-05-19 17:30 UTC (permalink / raw)
To: Benoît BOURNON <benoit.bournon@adelis.com>; +Cc: pgsql-sql
Thank you very much, but the database I'm using is not mine ....
that's why I cannot change anything ....
If I wont manage to write this replace function, I'll probably use this
solution ...
so thank you
best regards
R. K.
On Wed, 19 May 2004, [ISO-8859-1] Benoît BOURNON wrote:
> I try to reencode postgresql db ... all is running ... in your shell :
>
> - pg_dump db -f fileout
>
> - iconv fileout i do not remember parameters but iconv converts the file.
>
> - createdb db -U unicode (for example)
>
> - pgsql db < fileout_converted
>
>
> We change previous year the encoding of this website : www.ape-europe.org
> 'iso' to 'utf8' for problems of eastern countries ...
> all is running ...
>
> maybe you can try ...
>
> Kornelije wrote:
>
> > Hi,
> >
> > I need to write a function which has to replace a character with two
> > or three other characters ....
> >
> > I have some data in the DB and when I retrieve this data, I have
> > sorting problems because I use specific Croatian characters ....
> > I know that the DB is not setup properly, but I cannot change anything
> > now because some data could be lost ....
> >
> > So I need to change specific Croatian characters with some other
> > standard characters in order to sort the data properly ....
> >
> > so, if anybody knows something or has such function, please, let me
> > know ....
> >
> > thanks
> >
> > R.K.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Replace function ...
2004-05-17 14:58 Replace function ... Kornelije <krabuzin@foi.hr>
@ 2004-05-19 17:31 ` Kornelije Rabuzin <krabuzin@foi.hr>
2 siblings, 0 replies; 10+ messages in thread
From: Kornelije Rabuzin @ 2004-05-19 17:31 UTC (permalink / raw)
To: BenLaKnet <benlaknet@icqmail.com>; +Cc: pgsql-sql
thanks ....
If I don't find some other solution, I'll do it .....
so, thanks ...
best regards
Kornelije
On Wed, 19 May 2004, BenLaKnet wrote:
> I try to reencode postgresql db ... all is running ... in your shell :
>
> - pg_dump db -f fileout
>
> - iconv fileout i do not remember parameters but iconv converts the file.
>
> - createdb db -U unicode (for example)
>
> - pgsql db < fileout_converted
>
>
> We change previous year the encoding of this website : www.ape-europe.org
> 'iso' to 'utf8' for problems of eastern countries ...
> all is running ...
>
> maybe you can try ...
>
> Kornelije wrote:
>
> > Hi,
> >
> > I need to write a function which has to replace a character with two
> > or three other characters ....
> >
> > I have some data in the DB and when I retrieve this data, I have
> > sorting problems because I use specific Croatian characters ....
> > I know that the DB is not setup properly, but I cannot change anything
> > now because some data could be lost ....
> >
> > So I need to change specific Croatian characters with some other
> > standard characters in order to sort the data properly ....
> >
> > so, if anybody knows something or has such function, please, let me
> > know ....
> >
> > thanks
> >
> > R.K.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-05-19 17:31 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-28 15:02 replace function rute solipa <rutes@eselx.ipl.pt>
2003-04-28 15:08 ` Rajesh Kumar Mallah <rmallah@trade-india.com>
2003-04-28 15:12 ` rute solipa <rutes@eselx.ipl.pt>
2003-04-28 15:45 ` Rajesh Kumar Mallah <rmallah@trade-india.com>
2003-04-28 15:13 ` Victor Yegorov <viy@nordlb.lv>
2004-05-17 14:58 Replace function ... Kornelije <krabuzin@foi.hr>
2004-05-17 19:34 ` Re: Replace function ... Yasir Malik <ymalik@cs.stevens-tech.edu>
2004-05-19 10:24 ` Re: Replace function ... Benoît BOURNON <benoit.bournon@adelis.com>
2004-05-19 17:30 ` Re: Replace function ... Kornelije Rabuzin <krabuzin@foi.hr>
2004-05-19 17:31 ` Re: Replace function ... Kornelije Rabuzin <krabuzin@foi.hr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox