And if you have plperl installed, something like this is even easier:
create function texttrim(text) returns text as '$_[0] =~ s/\\W//g; return
$_[0]' language 'plperl';
(I just read the docs) :-)
> -----Original Message-----
> From: Jeff Eckermann [SMTP:jeckermann@verio.net]
> Sent: Tuesday, April 10, 2001 5:41 PM
> To: pgsql-sql@postgresql.org
> Subject: RE: Re: select substr???
>
> Regular expressions make this much easier. The below could be shortened
> to:
>
> create function ComparisonString(text) returns text as '
> declare
> t alias for $1;
> r text;
> c char;
> begin
> if t is null or t !~ ''[^a-zA-Z0-9]''
> then
> return t;
> end if;
> r = '''';
> for i in 1 .. char_length(t) loop
> c = substring(t from i for 1);
> if c ~ ''[a-zA-Z0-9]''
> then
> r = r || c;
> end if;
> end loop;
> return r;
> end;
> ' language 'plpgsql' with (IsCachable);
>
>
>
> > -----Original Message-----
> > From: Albert REINER [SMTP:areiner@tph.tuwien.ac.at]
> > Sent: Tuesday, April 10, 2001 11:38 AM
> > To: pgsql-sql@postgresql.org
> > Subject: Re: Re: select substr???
> >
> > On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote:
> > > Hi,
> > >
> > > I have postgres 6.x (where x is something).
> > >
> > > I have the following list of data
> > >
> > > data
> > > --------
> > > ABC*
> > > ABC
> > > ABC-
> > ...
> > > what I want to do is 'select distinct(data) [ignoring non alphanumeric
> > > characters] order by data'
> >
> > somewhere I use the following, which might be adapted to do what you
> > want. I am sure there are more elegant ways of doing this, though.
> >
> > create function ComparisonString(text) returns text as '
> > declare
> > t text;
> > r text;
> > c char;
> > ns bool;
> > begin
> > if $1 is null then
> > return NULL;
> > end if;
> > t = lower(trim(both $1));
> > r = '''';
> > ns = false;
> > for i in 1 .. char_length(t) loop
> > c = substring(t from i for 1);
> > if c = '' '' then
> > if ns then
> > r = r || '' '';
> > end if;
> > ns = false;
> > else
> > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0
> > then
> > r = r || c;
> > ns = true;
> > end if;
> > end if;
> > end loop;
> > return trim(both r);
> > end;
> > ' language 'plpgsql' with (IsCachable);
> >
> > Albert.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
