Re: Removing spaces

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> I would like to remove the trailing blanks between the last character
> and the newline. I would like to use replace with a regx like
> /[ ]+\n/\n/ but it does not seem to work.

I do not believe you can do this in straight SQL, as the replace() function
does not use regular expressions. However, in perl and other regex-capable
languages, it is very simple:

$string =~ s/ +\n/\n/g;

If you need a way to do it within the database, here is a plpgsql function
I whipped up for the occasion:

CREATE OR REPLACE FUNCTION noendspace(text) RETURNS text LANGUAGE 'plpgsql' AS
'
DECLARE

oldtext ALIAS FOR $1;
newtext TEXT := \'\';
spaces INTEGER := 0;
mypos INTEGER := 1;
mychar TEXT;

BEGIN

LOOP

SELECT SUBSTRING(oldtext,mypos,1)
INTO mychar;

IF mychar = \' \' THEN
spaces := spaces + 1;
ELSE
IF mychar = \'\\n\' THEN
spaces := 0;
ELSE
WHILE spaces LOOP
newtext := newtext || \' \';
spaces := spaces - 1;
END LOOP;
END IF;
newtext := newtext || mychar;
END IF;

EXIT WHEN mychar = \'\';
mypos := mypos + 1;

END LOOP;

RETURN newtext;

END;

';

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200302201053

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+VPtUvJuQZxSWSsgRAiDkAKDY7K0bg3itMZSh9bDaX5mFv/FqnwCgigTU
R6pvxOe8vpQNHDroRtMJjB4=
=TVWf
-----END PGP SIGNATURE-----