What's The Best Approach To Do Keyword Searching On A Text Field?

Question

I have a database table which holds meta data about images, the field in concern is the caption field. I want users to be able to enter keywords into a textbox and have the app return a selection of images that match the keywords based on their caption.

I already have the code that returns an array of the individual keywords entered by the user but whats the best way to do the comparison. So I'm thinking along the lines of...

foreach (Image image in Images)
{
    foreach (string keyword in keywords)
    {
        if (image.Caption.Contains(keyword))
        {
            imageCollection.Add(image);
            break;
        }
    }
}

But this just seems a little too simplistic because it wouldn't support matching whole words only. Not to mention special characters, punctuation etc.

I feel like Regex should be used here but i'm no Regex expert. Or should I be breaking up the caption into individual words and processing the comparison on words one by one. Looking for some suggestions really!

I'm writing in c# but can be language agnostic I think

EDIT: I'm also quite interested in weighting results based on number of keywords matched. But i'm not trying to recreate Google images here!

Answer 1

Probably the best way to do this would be to use full-text index on the caption field in the database. Let the database do the work for you!