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 17:41:26 -0500
Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094A0C@NEZU> (raw)

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



view thread (8+ messages)  latest in thread

Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094A0C@NEZU>
Permalink:  ../08CD1781F85AD4118E0800A0C9B8580B094A0C@NEZU/
Also on:    postgresql.org/message-id/08CD1781F85AD4118E0800A0C9B8580B094A0C@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: <08CD1781F85AD4118E0800A0C9B8580B094A0C@NEZU>
  References: <08CD1781F85AD4118E0800A0C9B8580B094A0C@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