r trim of characters other than space

am 09.02.2006, um 11:54:43 +0530 mailte surabhi.ahuja folgendes:

Hi
hi i have varchar type fiels in a table.

i want to make the following check,

if it is having carets in the end, then those carets be removed.

so if i get a string like abc def^^^^

i should be able to get abc def

Which version? I have 8.1 and can use regexp_replace for this:

test=# select 'abc def^^^^';
?column?
-------------
abc def^^^^
(1 row)

test=# select regexp_replace('abc def^^^^','[\\^]*$','');
regexp_replace
----------------
abc def
(1 row)

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

"surabhi.ahuja" <surabhi.ahuja@iiitb.ac.in> writes:

i want to make the following check,
if it is having carets in the end, then those carets be removed.
so if i get a string like abc def^^^^
i should be able to get abc def

Per SQL spec:

regression=# select trim(trailing '^' from 'abc def^^^^');
rtrim
---------
abc def
(1 row)

regards, tom lane

but how should i do it within a stored procedure
something like:

CREATE OR REPLACE FUNCTION insert(varchar(65),varchar(65),date,varchar(256)) RETURNS retval AS'
DECLARE
patName text;
BEGIN

patName := trim($1);
select trim(trailing `^` from patName) INTO patName;
trim(patName);

this seems to be giving syntax error

thanks,
regards
Surabhi

________________________________

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thu 2/9/2006 12:49 PM
To: surabhi.ahuja
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] r trim of characters other than space

***********************
Your mail has been scanned by iiitb VirusWall.
***********-***********

"surabhi.ahuja" <surabhi.ahuja@iiitb.ac.in> writes:

i want to make the following check,
if it is having carets in the end, then those carets be removed.
so if i get a string like abc def^^^^
i should be able to get abc def

Per SQL spec:

regression=# select trim(trailing '^' from 'abc def^^^^');
rtrim
---------
abc def
(1 row)

regards, tom lane

You need to use two single quotes around ^ (like ''^'') or use the
dollar quoting approach.

http://www.postgresql.org/docs/8.1/static/plpgsql-development-tips.html

surabhi.ahuja wrote:

patName := trim($1);
select trim(trailing `^` from patName) INTO patName;
trim(patName);

I think this line should contain an assignment.

--
Alban Hertroys