nikademus nikademus - 3 months ago 4x
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:

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:

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:

4 (sum of weight for sentence 1)
6 (sum of weight for sentence 2)


This should get you started:

  , 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.