select exact term

[Marc Fromm]

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

[Douglas J Hunley]

Post by Marc Fromm
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

[Craig James]

Post by Marc Fromm
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]

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:***@emolecules.com]
Sent: Thursday, March 28, 2013 11:05 AM
To: Marc Fromm
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] select exact term


On Thu, Mar 28, 2013 at 10:51 AM, Marc Fromm <***@wwu.edu<mailto:***@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

[Tom Lane]

Post by Marc Fromm
$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.

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

[Marc Fromm]

Thanks Tom, I just discovered that. I reworked the php so the quotes surround the regexp.

-----Original Message-----
From: Tom Lane [mailto:***@sss.pgh.pa.us]
Sent: Thursday, March 28, 2013 1:07 PM
To: Marc Fromm
Cc: Craig James; pgsql-***@postgresql.org
Subject: Re: [ADMIN] select exact term

Post by Marc Fromm
$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.

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

[Sergey Konoplev]

Post by Marc Fromm
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 [phone], Russia [phone], [phone]
Skype: gray-hemp
Jabber: ***@gmail.com