Thread: select exact term
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.
Thanks
Marc
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.
Thanks
Marc
http://www.postgresql.org/docs/9.2/static/functions-matching.html
Douglas J Hunley (doug.hunley@gmail.com)
Twitter: @hunleyd Web: douglasjhunley.com
G+: http://goo.gl/sajR3
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
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:cjames@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.Fromm@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
Marc Fromm <Marc.Fromm@wwu.edu> writes:
> 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
wordart.
Your php app must not be bothering to check for errors :-( ... that's
invalid SQL syntax. The bracket constructs are part of the regexp
string and need to be inside the single quotes.
regards, tom lane
Thanks Tom, I just discovered that. I reworked the php so the quotes surround the regexp.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 28, 2013 1:07 PM
To: Marc Fromm
Cc: Craig James; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] select exact term
Marc Fromm <Marc.Fromm@wwu.edu> writes:
> 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
wordart.
Your php app must not be bothering to check for errors :-( ... that's invalid SQL syntax. The bracket constructs are
partof the regexp string and need to be inside the single quotes.
regards, tom lane
On Thu, Mar 28, 2013 at 10:51 AM, Marc Fromm <Marc.Fromm@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? In addition to what has been suggested before you may find interesting full text search abilities http://www.postgresql.org/docs/9.2/static/textsearch.html. It allows to do more complex searches like this: SELECT title, ts_rank_cd(textsearch, query) AS rank FROM apod, to_tsquery('neutrino|(dark & matter)') query WHERE query @@ textsearch ORDER BY rank DESC LIMIT 10; -- Kind regards, Sergey Konoplev Database and Software Consultant Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@gmail.com