Sql: Multiple keywords search for complaints data set

Question

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

-------------------------

Debit Card

Credit Card

ATM

Loans

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!!

Answer 1

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]