Received: from frithjof.reiner (b104.dialin.tuwien.ac.at [192.35.240.234]) by postgresql.org (8.11.3/8.11.1) with SMTP id f3AJlG862226 for ; Tue, 10 Apr 2001 15:47:20 -0400 (EDT) (envelope-from areiner@tph.tuwien.ac.at) Received: (qmail 3639 invoked by uid 500); 10 Apr 2001 16:38:15 -0000 From: "Albert REINER" Date: Tue, 10 Apr 2001 18:38:15 +0200 To: pgsql-sql@postgresql.org Subject: Re: Re: select substr??? Message-ID: <20010410183815.C3219@frithjof> Mail-Followup-To: pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: ; from tim@celestialdesign.co.uk on Mon, Apr 09, 2001 at 06:05:55PM +0100 X-Archive-Number: 200104/143 X-Sequence-Number: 2491 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.