From: | psql-mail(at)freeuk(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Concatenating two Text fields from the same tuple |
Date: | 2003-07-03 17:16:35 |
Message-ID: | E19Y7hD-0007gl-00@buckaroo.freeuk.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Apologies if this is a repost - I tried sending it yesterday and haven'
t seen it in the forum yet.
I am currently writing a perl script to convert the string a user
supplies to a search engine into SQL. The user supplies a string in the
same foramt as google uses - e.g. "cat -dog" finds records containing
cat but not dog.
The code i have written works well but i have just realised that if (
unknown to the user) the DB needs to search across more than one
attribute, a number of problems arise. For the above query the code
currently outputs WHERE head ILIKE '%cat%' AND head NOT ILIKE '%dog%'
OR body ILIKE '%cat%' AND body NOT ILIKE '%dog%';
which is equivalent to:
WHERE ( attr1 ILIKE '%cat%' AND attr1 NOT ILIKE '%dog%') OR ( attr2
ILIKE '%cat%' AND attr2 NOT ILIKE '%dog%');
This gives undesired behaviour - if 'cat' is found in the attr1 , and '
dog' is not found in attr1 of then the tuble is returned, even if dog
is found in attr2 of the tuple.
What is really wanted is: WHERE ( attr1 ILIKE '%cat%' OR attr2 ILIKE '%
cat%' ) AND attr1 NOT ILIKE '%dog%' AND attr2 NOT ILIKE '%dog%;
Question1 1: Is there already a google style search string parser out
there somewhere?
Question 2: If not - is it possible for me to concatenate attr1 and
attr2 before i do the search as this would remove the logic problems. (
and if it is possible - is it efficient?)
Question 3: If its not possible to concatenate them - any other
sugestions?!
Thanks!
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-07-03 17:18:09 | Are we backwards on the sign of timezones? |
Previous Message | Andrew Sullivan | 2003-07-03 17:00:31 | Re: [GENERAL] interesting PHP/MySQL thread |