nikademus nikademus - 5 months ago 8
SQL Question

compare a string from a table and compare each keyword from another table

I have two tables:

table 1 contains a list of sentences:

Sentence
----------------------------------------------------------------
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua
Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur


and table 2 contains a list of keywords and weights:
tblKeyword

Keyword Weight
----------------------------------------------------------------
dolor sit 1
elit 3
foobar 10


For each sentence in table 1, I would like to get a sum of Weight of all keywords within table 2 that could be found in the sentence from table 1. How can I do this in SQL without using cursor?

expected result:

Result
------
4 (sum of weight for sentence 1)
6 (sum of weight for sentence 2)
...

Answer

This should get you started:

SELECT
  S.sentence
  , SUM(K.weight) AS total_weight
FROM Sentence S
JOIN Keyword K
  ON CHARINDEX(K.keyword, S.sentence) > 0
GROUP BY S.sentence
;

(Sorry: No access to a SQL Server instance to verify. Tried with MySQL and replaced INSTR with CHARINDEX.)

Please comment, if and as this requires adjustment / further detail.