pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Eckermann <jeckermann@verio.net>
To: pgsql-sql@postgresql.org
Subject: RE: Re: select substr???
Date: Tue, 10 Apr 2001 18:08:08 -0500
Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094A0D@NEZU> (raw)

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



view thread (8+ messages)  latest in thread

Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094A0D@NEZU>
Permalink:  ../08CD1781F85AD4118E0800A0C9B8580B094A0D@NEZU/
Also on:    postgresql.org/message-id/08CD1781F85AD4118E0800A0C9B8580B094A0D@NEZU

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jeckermann@verio.net
  Subject: Re: select substr???
  In-Reply-To: <08CD1781F85AD4118E0800A0C9B8580B094A0D@NEZU>
  References: <08CD1781F85AD4118E0800A0C9B8580B094A0D@NEZU>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox