Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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

  select
        ObjectID
        ,value
        ,(len(value) - len(replace(value,'love',''))) / 4 as occurs
        from exampleTable
        where value like '%love%'
share|improve this answer
    
Freaking brilliant... Thanks! –  VoidKing Jun 27 '13 at 20:36
    
Is this supported by SQL CE? I get a parsing error on the equals sign after "occurs" –  VoidKing Jun 27 '13 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 Jun 27 '13 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 Jun 27 '13 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 Jun 27 '13 at 20:54

With the aid of a Numbers table:

CREATE TABLE Numbers
  ( i INT NOT NULL PRIMARY KEY ) ;

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 
FROM                                       
    (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 ;
share|improve this answer
    
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 Jun 27 '13 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 Jun 27 '13 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. –  ypercube Jun 27 '13 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 Jun 27 '13 at 20:57
    
About the (1),(2), ... (200), right, you can't just write ..., I'll edit. –  ypercube Jun 27 '13 at 20:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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