I am working on a complaints data-set & I am looking for matching keywords from the keyword dictionary against the data-set.
Here is the keyword dictionary -
Sample Keyword Dictionary
Sample data-set -
And, Sample Output -
That is, I would like the keywords from the keyword dictionary to be matched against the "complaint description" column in my data-set. And, I would like only the matched keywords to show up in the "Keywords Matched" column & count of total keywords matched in the "Count" column.
This is just a sample & my actual data-set is huge plus it might involve "Complaint Description" in foreign language as well. Can you help me on how to go about it?
Thanks a lot!!
Assume your sample data table is
[Sample Data-set] and your have your keywords saved in a table called
[Keyword] (with column name
Keyword). Try this:
WITH CTE AS ( SELECT Detail.*, [Keywords Matched] = STUFF((select ',' + Keyword FROM KEYWORD as b WHERE Detail.[Complaint Description] like '%' + b.Keyword + '%' ORDER BY Keyword DESC FOR XML PATH('')), 1,1,'') FROM [Sample Data-set] as Detail GROUP BY Detail.BranchID, Detail.Portfolio, Detail.[Complaint Description] ) SELECT *, len([Keywords Matched]) - len(replace([Keywords Matched], ',','')) + 1 as [Count] FROM CTE GROUP BY BranchID, Portfolio, [Complaint Description], [Keywords Matched]