I am struggling with the syntax. In php I create my where clause as shown, using ~* for case insensitive: $search = "art"; $strSQL2 = "WHERE (title ~* [[:<:]]'$search'[[:>:]] OR description ~* [[:<:]]'$search'[[:>:]]) ";
When executed zero records are returned even though the ILIKE statement shown below returns records that do have the word art. $search = "art"; $strSQL2 = "WHERE (title ILIKE '%$search%' OR description ILIKE '%$search%') "; Thanks for the insight. From: Craig James [mailto:cja...@emolecules.com] Sent: Thursday, March 28, 2013 11:05 AM To: Marc Fromm Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] select exact term On Thu, Mar 28, 2013 at 10:51 AM, Marc Fromm <marc.fr...@wwu.edu<mailto:marc.fr...@wwu.edu>> wrote: Is there a way to create a select statement that will select a record if the exact term is found in a field that contains the text to describe something? If I create a select statement using WHERE description LIKE 'art' I get every record that has words like depart, start and so on. If I create a select statement using WHERE description = 'art' I get no results even though the word art is in some records description field. Use a regular expression instead of LIKE, and the left- and right-word-boundary expressions (see section 9.7 of the Postgres manual): db=> select 'the quick brown fox' ~ '[[:<:]]brown[[:>:]]'; ?column? ---------- t => select 'the quick brown fox' ~ '[[:<:]]own[[:>:]]'; ?column? ---------- f Craig
