pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedselect exact term
7+ messages / 5 participants
[nested] [flat]
* select exact term
@ 2013-03-28 17:51 Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-28 17:59 ` Re: select exact term Douglas J Hunley <doug.hunley@gmail.com>
2013-03-28 18:05 ` Re: select exact term Craig James <cjames@emolecules.com>
2013-03-29 00:06 ` Re: select exact term Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Marc Fromm @ 2013-03-28 17:51 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select exact term
2013-03-28 17:51 select exact term Marc Fromm <Marc.Fromm@wwu.edu>
@ 2013-03-28 17:59 ` Douglas J Hunley <doug.hunley@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Douglas J Hunley @ 2013-03-28 17:59 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
On Thu, Mar 28, 2013 at 1:51 PM, 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.****
>
> ** **
>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select exact term
2013-03-28 17:51 select exact term Marc Fromm <Marc.Fromm@wwu.edu>
@ 2013-03-28 18:05 ` Craig James <cjames@emolecules.com>
2013-03-28 19:02 ` Re: select exact term Marc Fromm <Marc.Fromm@wwu.edu>
2 siblings, 1 reply; 7+ messages in thread
From: Craig James @ 2013-03-28 18:05 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select exact term
2013-03-28 17:51 select exact term Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-28 18:05 ` Re: select exact term Craig James <cjames@emolecules.com>
@ 2013-03-28 19:02 ` Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-28 20:06 ` Re: select exact term Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Marc Fromm @ 2013-03-28 19:02 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-admin
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<mailto: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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select exact term
2013-03-28 17:51 select exact term Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-28 18:05 ` Re: select exact term Craig James <cjames@emolecules.com>
2013-03-28 19:02 ` Re: select exact term Marc Fromm <Marc.Fromm@wwu.edu>
@ 2013-03-28 20:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-03-28 20:30 ` Re: select exact term Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2013-03-28 20:06 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: Craig James <cjames@emolecules.com>; pgsql-admin
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 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select exact term
2013-03-28 17:51 select exact term Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-28 18:05 ` Re: select exact term Craig James <cjames@emolecules.com>
2013-03-28 19:02 ` Re: select exact term Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-28 20:06 ` Re: select exact term Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-03-28 20:30 ` Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Marc Fromm @ 2013-03-28 20:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select exact term
2013-03-28 17:51 select exact term Marc Fromm <Marc.Fromm@wwu.edu>
@ 2013-03-29 00:06 ` Sergey Konoplev <gray.ru@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Sergey Konoplev @ 2013-03-29 00:06 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2013-03-29 00:06 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-28 17:51 select exact term Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-28 17:59 ` Douglas J Hunley <doug.hunley@gmail.com>
2013-03-28 18:05 ` Craig James <cjames@emolecules.com>
2013-03-28 19:02 ` Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-28 20:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-03-28 20:30 ` Marc Fromm <Marc.Fromm@wwu.edu>
2013-03-29 00:06 ` Sergey Konoplev <gray.ru@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox