How to search a phrase inside a single DB field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fabio Gava - 15 Jun 2007 11:11 GMT

I have this problem: all the keywords of an image are stored in a single
text field called "keywords". I created a catalog on that field.

A couple of samples of my keywords:

ImageA = Tower Bridge, landmark, bridge, illuminated, National Famous Place,
Thames
ImageB = architecture, bell tower, bridge, fountain, railing, Europe,
lighting, city

and now the query:

select
*
from phe_keywords
where contains(*,'"tower bridge"')

I get the two sampled rows in the rowset, which is not the desired result,
as you can guess.

I just wonder how do i tell full text catalog that i want "bell tower" and
"bridge" to be considered as not belonging to the same "sentence"?

Many thanks.

-fabio

Reply to this Message

ML - 15 Jun 2007 16:07 GMT

You're data seems clean but your model is not properly normalized.
Create a on-to-many relationship with each image and its keywords.

ML

---
http://milambda.blogspot.com/

Reply to this Message

Fabio Gava - 15 Jun 2007 16:21 GMT

> You're data seems clean but your model is not properly normalized.
> Create a on-to-many relationship with each image and its keywords.

this is where i stared from, but.. how do you manage the case where you need
images with "palm and beach"? you can't simply ask to full text, since each
keyword is "palm" OR "beach" and no result will be given.
You have to desctruc the simple "palm AND beach"..

Thanks for the suggestion, anyway

-f

Reply to this Message

Daniel Crichton - 15 Jun 2007 17:36 GMT

Fabio wrote on Fri, 15 Jun 2007 17:21:54 +0200:

>> You're data seems clean but your model is not properly normalized.
>> Create a on-to-many relationship with each image and its keywords.
[quoted text clipped - 7 lines]
>
> -f

How many terms do you envisage having in a search? With a normalised
approach, you could do this:

SELECT i.ImageID FROM
Images i
INNER JOIN CONTAINSTABLE(ImageKeys,Keywords,'palm') ik1
ON i.ImageID = ik1.[KEY]
INNER JOIN CONTAINSTABLE(ImageKeys,Keywords,'beach') ik2
ON i.ImageID = ik2.[KEY]

Images table:
------------
ImageID: image1

ImageKeys:
----------
ImageID: image1
Keywords: palm
ImageID: image1
Keywords: beach
ImageID: image1
Keywords: my phrase
ImageID: image2
Keywords: my
ImageID: image2
Keywords: phrase

where Images holds you image data, and ImageKeys holds your keywords/phrases
for each. As both ik1 and ik2 will return the same ImageID value the join
finds that row from the Images table.

For an OR it's a bit messier though, as you have to have LEFT joins rather
than INNER joins. For phrasing you just do:

SELECT i.ImageID FROM
Images i
INNER JOIN CONTAINSTABLE(ImageKeys,Keywords,'"my phrase"') ik1
ON i.ImageID = ik1.[KEY]

and you'll only get image1, because image2 has it's keywords split over 2
rows. In your existing configuration you'd get image2 back in your results
too.

However, performance on this might not be suitable.

Another option is to use a word as a delimiter rather than a comma (or other
work breaker), such as MYDELIMTERVAL

eg.

Keywords: palm MYDELIMITERVAL beach

then when a phrase search for "palm beach" won't match, but 'palm AND beach'
will. Just make sure you filter the word MYDELIMITERVAL out of your search
query builder or else you'll return every row :)

Dan

Reply to this Message

Fabio Gava - 15 Jun 2007 18:52 GMT

> How many terms do you envisage having in a search? With a normalised
> approach, you could do this:
[quoted text clipped - 5 lines]
> INNER JOIN CONTAINSTABLE(ImageKeys,Keywords,'beach') ik2
> ON i.ImageID = ik2.[KEY]

Ok, this was my frist solution, but, as you pointed out, it requires parsing
full text queries, that I would like to use "as is" with all variants (like
NEAR or parenthesis).

> Another option is to use a word as a delimiter rather than a comma
> (or other work breaker), such as MYDELIMTERVAL

This is a good suggestions, thanks. I just hoped there was some more
"official" way to tell the system to use the comma as this delimeter. Anyway
it's not a big issue adding it since I'm using a SF to keep the text aligned
on keyword changes.

-fabio

Reply to this Message