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