-----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@turnstep.com
PGP Key: 0x14964AC8 200302201053
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+VPtUvJuQZxSWSsgRAiDkAKDY7K0bg3itMZSh9bDaX5mFv/FqnwCgigTU
R6pvxOe8vpQNHDroRtMJjB4=
=TVWf
-----END PGP SIGNATURE-----