Received: from pegasus.freibergnet.de (pegasus.freibergnet.de [194.123.255.9]) by hub.org (8.9.3/8.9.3) with ESMTP id CAA93020 for ; Tue, 31 Aug 1999 02:30:09 -0400 (EDT) (envelope-from holm@pegasus.freibergnet.de) Received: (from holm@localhost) by pegasus.freibergnet.de (8.9.3/8.9.1) id IAA24670; Tue, 31 Aug 1999 08:29:20 +0200 (CEST) (envelope-from holm) Date: Tue, 31 Aug 1999 08:29:20 +0200 From: Holm Tiffe To: Tom Lane Cc: holm@freibergnet.de, pgsql-sql@postgreSQL.org Subject: Re: [SQL] problem with select Message-ID: <19990831082920.A23046@pegasus.freibergnet.de> Reply-To: holm@freibergnet.de References: <19990830140452.B21897@pegasus.freibergnet.de> <5585.936024234@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Mailer: Mutt 0.95i In-Reply-To: <5585.936024234@sss.pgh.pa.us>; from Tom Lane on Mon, Aug 30, 1999 at 10:43:54AM -0400 Organization: FreibergNet Internet Services Priority: normal X-Phone: +49-3731-781279 X-Fax: +49-3731-781377 X-PGP-fingerprint: 86 EC A5 63 B5 28 78 13 8B FC E9 09 04 6E 86 FC Tom Lane wrote: > Holm Tiffe writes: > > What I try to find is a solution for a search in this table like this: > > > select distinct code,category from products where code ~* 'abc' or > > category ~* 'abc' or manufacturer ~* 'abc' or description ~* 'abc' > > or comment ~* 'abc' order by code; > > > So far so good, but I have the problem that I have the value 'abc' > > only one times ! > > How about > > select distinct code,category from products where > (code || category || manufacturer || ...) ~* 'abc'; > > Actually you'd probably want to also concatenate some separator markers, > maybe (code || '|' || category || ...), so that you didn't get bogus > matches across fields, like where code ends in 'a' and category starts > with 'bc'. > > Note that this select will be a bit of a memory hog because > text-slinging is very wasteful of space in 6.5 (the intermediate results > from the concatenate operators don't get freed till end of transaction). > So it might not be workable if you have a large database. I hope to see > that fixed for 6.6 or 6.7. > > regards, tom lane Hm, clever. It is almost working, my database isn't that big that memory becomes a problem; the machine has 512MB. But why is nothing found if one value of a column contains NULL ? select distinct code,category from products where (code || '|' || category || '|' || comment || '|' || description || '|' || desc_de) ~* 'kde'; code|category ------+-------- 06-001|KDE (1 row) shop=> update products set comment = Null where code ='06-001'; UPDATE 1 shop=> select (comment || '|' || code) from products where code ~* '06-001'; ?column? -------- (1 row) Holm -- FreibergNet Systemhaus GbR Holm Tiffe * Administration, Development Systemhaus für Daten- und Netzwerktechnik phone +49 3731 781279 Unternehmensgruppe Liebscher & Partner fax +49 3731 781377 D-09599 Freiberg * Am St. Niclas Schacht 13 http://www.freibergnet.de/