Received: from nezu.gs.verio.net (nezu.gs.verio.net [204.27.65.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3AMfU887797 for ; Tue, 10 Apr 2001 18:41:30 -0400 (EDT) (envelope-from jeckermann@verio.net) Received: by NEZU with Internet Mail Service (5.5.2650.21) id <2HD3M3BY>; Tue, 10 Apr 2001 17:41:27 -0500 Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094A0C@NEZU> From: Jeff Eckermann To: pgsql-sql@postgresql.org Subject: RE: Re: select substr??? Date: Tue, 10 Apr 2001 17:41:26 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain X-Archive-Number: 200104/145 X-Sequence-Number: 2493 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