3

Not sure if this is possible

I have a bunch of 100 keywords and I'm running a MATCH AGAINST query to see if those keywords are present in the table - The query works fine:

SELECT * FROM questions_new WHERE MATCH (question_title,question)
AGAINST ('depreciation amortization npv dcf "discounted cash flow" "cash flow statement" "current assets"' IN BOOLEAN MODE);

This is just a sample query with few keywords

The results are returned as follows:

question_id | question_title            |  question
    1       | what is depreciation      |  I am trying to do this DCF calculation......
    2       | Need help with this       |  what is a cash flow statement
    3       | Cannot solve this problem |  Can you give more examples on npv

This is a sample result set. Obviously my result set is much larger.

Now looking at the question_title or question - its very difficult for me to find out which keyword that was matched against as the keyword list is huge.

I was wondering if its possible for me to include the matched keyword in the result set as shown below

question_id |        keyword      | question_title            |  question
    1       | depreciation, DCF   | what is depreciation      |  I am trying to do this DCF calculation......
    2       | cash flow statement | Need help with this       |  what is a cash flow statement
    3       | npv                 | Cannot solve this problem |  Can you give more examples on npv

As you can see the first result, shows 2 keywords that were matched - Depreciation and DCF.

Is there anyway to do this.

Thanks in advance Appreciate your help

Gublooo
  • 2,550
  • 8
  • 54
  • 91

2 Answers2

1

Try this, its working fine for me.

SELECT column1, column2,
cASE when column1 like '%word1%' then "word1" 
when column1 like '%word2%' then "word2" 
when column1 like '%word3%' then "word3" 
when column1 like '%word4%' then "word4" end  as matched_word
FROM table_name WHERE MATCH (column1,column2)
AGAINST ('"word1" "word2" "word3" "word4"' IN BOOLEAN MODE);

You can use second case statement for column2 respectively. If you want to show both columns matched_value together use this- 

SELECT column1, column2,
ifnull((cASE when column1 like '%word1%' then "word1" 
when column1 like '%word2%' then "word2" 
when column1 like '%word3%' then "word3" 
when column1 like '%word4%' then "word4" end),
(cASE when column2 like '%word1%' then "word1" 
when column2 like '%word2%' then "word2" 
when column2 like '%word3%' then "word3" 
when column2 like '%word4%' then "word4" end))  as matched_word
FROM table_name WHERE MATCH (column1,column2)
AGAINST ('"word1" "word2" "word3" "word4"' IN BOOLEAN MODE);
Ankit Sharma
  • 3,923
  • 2
  • 29
  • 49
0

with a case/if. 

concat(
   if(yourcolumn like"%keyword%", 'keyword ', ''),
   if(yourcolumn like"%keyword2%", 'keyword2 ', ''),
   if(yourcolumn like"%keyword3%", 'keyword3 ', '')
) as found_keywords
AdrianBR
  • 2,762
  • 1
  • 15
  • 29