Thread: Removing spaces

I have a field of type text that has a value like

'hello   \nworld   \n'

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.

On Wednesday 19 Feb 2003 5:07 pm, Dave Smith wrote:
> I have a field of type text that has a value like
>
> 'hello   \nworld   \n'
>
> 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.

The replace function doesn't handle regexps AFAIK - you could look at
SUBSTRING() in the POSIX Regexp section of the functions reference but I
don't think that's going to do it either.

The problem is you seem to want to remove spaces after "hello" and "world" - I
think that means you need perl regexps or a loop.

Unless some posix guru steps forward, I'd say the easiest way is to build a
loop that repeatedly calls replace(my_string,' \n','\n')
--
  Richard Huxton

Don't know if this is what you are looking for but the following will
remove them

select translate('hello   \nworld   \n',' ','');

HTH

Darren

On Wed, 19 Feb 2003, Richard Huxton wrote:

> On Wednesday 19 Feb 2003 5:07 pm, Dave Smith wrote:
> > I have a field of type text that has a value like
> >
> > 'hello   \nworld   \n'
> >
> > 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.
>
> The replace function doesn't handle regexps AFAIK - you could look at
> SUBSTRING() in the POSIX Regexp section of the functions reference but I
> don't think that's going to do it either.
>
> The problem is you seem to want to remove spaces after "hello" and "world" - I
> think that means you need perl regexps or a loop.
>
> Unless some posix guru steps forward, I'd say the easiest way is to build a
> loop that repeatedly calls replace(my_string,' \n','\n')
>

--
Darren Ferguson

On Wednesday 19 Feb 2003 9:25 pm, Darren Ferguson wrote:
> Don't know if this is what you are looking for but the following will
> remove them
>
> select translate('hello   \nworld   \n',' ','');

But surely this will remove *all* spaces, wheras Dave seemed to want only
spaces before newline characters.

> > > 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.

--
  Richard Huxton

-----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-----