Is there any way I can query a SQL-Server-CE database that will return the number of matches found in a cell value using LIKE (or MATCH AGAINST or any other method I'm not aware of, really)?
EXAMPLE table, "exampleTable":
**ObjectID** **value** 1 I Love Lemonade 2 I Love Love
Using a query like this one:
SELECT * FROM exampleTable WHERE value LIKE '%love%', I can easily find any entries where the value contains the word (or has a word where part of it contains the word) "love." I would like to be able to know just "how many" matches were found (for sake of search priority) in the matched fields, so that they can be displayed as more relevant. I realize that I'm probably asking a lot out of a simple query, however, I also know how powerful SQL can be, too, so thought I would ask if this was possible and if not, if there is any other approach I might take (that won't force me to rewrite the database).
I thought of using sub-queries somehow, but there is no way (that I have been able to find) to combine "LIKE" and "IN". Also, I am not even sure using a sub-query would get the job done since I'm working with a relational database where one main table relates to 8 others and the "8 other" tables have the values I want to search on, but the main table has the location I need (although, I suppose I don't have a problem adding a "location" column to these "8 other" tables, as it would be helpful in other areas, too).
It's entirely possible that I didn't design the "relational" part of the relational database to perfection, but this is the first time I have designed (although, certainly not worked with) a relational database by myself, and, at least, it works well with the back-end of my website (yes this site is a private partial CMS).
Any help is appreciated, and thank you for your time!