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!

2 Answers 2


You compare the length of the string with and without the text you're searching for:

        ,(len(value) - len(replace(value,'love',''))) / 4 as occurs
        from exampleTable
        where value like '%love%'
  • Is this supported by SQL CE? I get a parsing error on the equals sign after "occurs"
    – VoidKing
    Commented Jun 27, 2013 at 20:38
  • Okay that did the trick, however, you may need to look at your calculations again if, indeed, occurs is supposed to reflect the number of times the entry was found. When I used this "occurs" seemed to equal double the amount of entries it found.
    – VoidKing
    Commented Jun 27, 2013 at 20:45
  • are you searching for 'love'? If you're changing the search string in your testing, you need to change the 4 to match the length of the string.
    – SQLFox
    Commented Jun 27, 2013 at 20:50
  • 1
    ~DERP SELECT Intelligence FROM Myself WHERE payingAttention IS NOT NULL Sorry, yeah, coincidentally my search string was 8 characters long.
    – VoidKing
    Commented Jun 27, 2013 at 20:54
  • Just thought I would mention that you should probably use this: ,(len(value) - len(replace(UPPER(value), UPPER('love'), ''))) / 4 as occurs instead of this: ,(len(value) - len(replace(value,'love',''))) / 4 as occurs Because while LIKE is case insensitive, 'replace' is not, and won't return the appropriate number of occurences unless the cases are all the same. Of course, you could also use LOWER the same way, but either way this modification fixes the problem.
    – VoidKing
    Commented Jun 28, 2013 at 15:30

With the aid of a Numbers table:


INSERT INTO Numbers (i)
VALUES                                        -- fill the table up to 10
  (1), (2), ... (10) ; 

INSERT INTO Numbers (i)                       -- fill the table up to 100
SELECT m.maxi * a.i + b.i 
    (SELECT MAX(i) AS maxi FROM Numbers) m    -- if you run this once more  
  CROSS JOIN Numbers a                        -- it will get up to 10000
  CROSS JOIN Numbers b
WHERE a.i < m.maxi ; 

you can run this:

SELECT e.ObjectID, e.value, COUNT(*) AS matches
FROM exampleTable AS e 
  JOIN Numbers AS n
    ON  n.i <= LEN(e.value) + 1 - 4
    AND SUBSTRING(e.value, n.i, 4) = 'love' 
WHERE e.value LIKE '%love%' 
GROUP BY e.ObjectID, e.value ;
  • Your answer should work also, if I am understanding it correctly. When I "create" this table, you are talking about a permanent table aren't you?
    – VoidKing
    Commented Jun 27, 2013 at 20:50
  • And now for the noob question of the day, I can't write (1), (2), ... (200) right? I actually have to type out each number don't I? and this number would be the max amount of occurences I will actually care to register for the sake of priority, right? (did that make any sense?)
    – VoidKing
    Commented Jun 27, 2013 at 20:52
  • Permanent or temporary. It could also be a derived table or a CTE (if CE supports them.) It's clearer this way in my opinion and a permanent table of a few hundreds or thousands rows does take much space - while it can have other uses. Commented Jun 27, 2013 at 20:53
  • Yeah, I'm pretty sure CE doesn't support that, if I'm not mistaken, but that's okay, I think having it as a permanent table would make the search process faster anyway. Also, as you stated, it will have other uses. Thanks again! I +1ed you. Really wish I could accept two answers here...
    – VoidKing
    Commented Jun 27, 2013 at 20:57
  • About the (1),(2), ... (200), right, you can't just write ..., I'll edit. Commented Jun 27, 2013 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.