Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 74063476099 for ; Tue, 22 Oct 2002 12:55:26 -0400 (EDT) Received: from pd4mo1so.prod.shaw.ca (shawidc-mo1.cg.shawcable.net [24.71.223.10]) by postgresql.org (Postfix) with ESMTP id E8A55475FBF for ; Tue, 22 Oct 2002 12:55:25 -0400 (EDT) Received: from pd6mr1so.prod.shaw.ca (pd6mr1so-qfe3.prod.shaw.ca [10.0.141.216]) by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002)) with ESMTP id <0H4E00DQ03UJRQ@l-daemon> for pgsql-sql@postgresql.org; Tue, 22 Oct 2002 09:47:07 -0600 (MDT) Received: from pn2ml7so.prod.shaw.ca (pn2ml7so-qfe0.prod.shaw.ca [10.0.121.151]) by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002)) with ESMTP id <0H4E00KA13UJA9@l-daemon> for pgsql-sql@postgresql.org; Tue, 22 Oct 2002 09:47:07 -0600 (MDT) Received: from kimiko (h24-78-132-76.vc.shawcable.net [24.78.132.76]) by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002)) with SMTP id <0H4E00HK23UIUL@l-daemon> for pgsql-sql@postgresql.org; Tue, 22 Oct 2002 09:47:07 -0600 (MDT) Date: Tue, 22 Oct 2002 04:07:51 -0700 From: Vernon Wu Subject: Re: Can I search for an array in csf? In-reply-to: <3DB5498E.A12E8BF1@rodos.fzk.de> To: Christoph Haller Cc: pgsql-sql@postgresql.org Reply-To: vernonw@gatewaytech.com Message-id: MIME-version: 1.0 X-Mailer: Opera 6.05 build 1140 Content-type: text/plain; charset=iso-8859-2 Content-transfer-encoding: 7BIT X-Priority: 3 (Normal) X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/281 X-Sequence-Number: 9998 Hi, Christoph, Thanks for reminding me regular expression. The background of my question is about attributes of one element, say hobby for example. Different people have different hobbies. Inside of the application, hobbies are denoted in various characters. The selection I mentioned in the original mail refers to finding people who have certain hobbies in the case. I think regular expression is the most effective way for the usage. Thanks again. Vernon 10/22/2002 5:50:22 AM, Christoph Haller wrote: >> >> I thought I had made my case clear. Let me rephrase it. >> >> I have a character array, {'a', 'b', 'c', 'd'} for example. And this >array is stored in a DB table field as a string >of "a,b,c,d". >> Now, I want to find out whether the table field, or array, contains >any character set of {'c', 'e', 'h'}. My question > is >> whether SQL statement is appliable for this selection, or the data has >been retrieved and process in the application >> level. >> >> I hope I make th case clear this time. >> >Ok, now I see. >Maybe the Postgres POSIX Regular Expressions are what you are looking >for. >Suppose your array "a,b,c,d" is stored into a table field of type >character, >character varying or text. >Then, if you are searching for all entries containing a 'c', 'e', or >'h' character >your SELECT statement would look like > >SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ '[ceh]' ; > >Still not sure if you mean a sequence like "c,e,h" on the other hand. >Then >SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ 'c,e,h' ; >should do the trick - as long as the order of characters is identical. >A sequence of "e,c,h" would not show up, of course. > >Regular Expressions are far more powerful than these two examples can >show. >Refer to the related chapter in the documentation. >I hope this helps more than the other replies you've received telling >you >'Learn about DB-design first'. > >Regards, Christoph > >