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:[EMAIL PROTECTED]]
> Sent: Tuesday, April 10, 2001 11:38 AM
> To: [EMAIL PROTECTED]
> 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
