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@turnstep.com
PGP Key: 0x14964AC8 200302201053

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

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