SQL search Query for matching at least half of the keywords of array

Question

I have a Table schema.

Id, Name, Email, Description.

Description column will have a lot of text for each row. Now I want to perform a search in that column based on the words that user have entered. Let say the user have entered five words for search.

Busy
Good
Nature
Block
Sweet

Now I want to perform a search that if any row of description column matches any of at least three of the above five, it should give that row in the result set. I don't do not want all words match or a single match. I want at least three words to be matched in each row of Description column. I know about LIKE Statement But how can I make it so that I can get my desired Result. Ignoring Case sensitivity. How Can I do that? Can Anybody help me to make SQL query for my problem?

what DB are you using? This might be the right place to start http://stackoverflow.com/questions/8084230/optimizing-an-sql-search-using-a-column-for-keywords-and-a-variable-as-the-text — Daniel E., Mar 12 '14 at 23:03

score 1 · Answer 1 · answered Mar 12 '14 at 22:20

I'm sure there should be a lot of better solutions but I found funny this problem and here's a proposal

SELECT IF ( LENGTH(CONCAT(
    IF (Description LIKE '%busy%', '1', ''), 
    IF (Description LIKE '%good%', '1', ''), 
    IF (Description LIKE '%nature%', '1', ''), 
    IF (Description LIKE '%blck%', '1', ''), 
    IF (Description LIKE '%sweet%', '1', '')        
)) > 2, 'Matches', 'Does not match') FROM ....

XDD I haven't thought in efficiency at all

score 1 · Accepted Answer · edited Mar 14 '14 at 13:51

Olvathar's idea is right but it will only show the Matches or Does not match results. However, if you want to get the records against this control you should use if statement inside the where clause to show only the records where condition is true.

SELECT *  FROM TABLENAME WHERE Description IS NOT NULL AND IF ( LENGTH(CONCAT(
IF (Description LIKE '%busy%', '1', ''), 
IF (Description LIKE '%good%', '1', ''), 
IF (Description LIKE '%nature%', '1', ''), 
IF (Description LIKE '%blck%', '1', ''),
IF (Description LIKE '%sweet%', '1', '')
)) > 2, 1, 0)=1

Hope this will help.

SQL search Query for matching at least half of the keywords of array

2 Answers2