Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA48212 for ; Mon, 30 Aug 1999 10:44:33 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA05587; Mon, 30 Aug 1999 10:43:54 -0400 (EDT) To: holm@freibergnet.de cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] problem with select In-reply-to: Your message of Mon, 30 Aug 1999 14:04:52 +0200 <19990830140452.B21897@pegasus.freibergnet.de> Date: Mon, 30 Aug 1999 10:43:54 -0400 Message-ID: <5585.936024234@sss.pgh.pa.us> From: Tom Lane 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