SQL Server: how to compare two tables

Question

Hi.

I have problem with comparing two tables in SQL Server.

I have first table [Table1] with text column where I store my content and second table [table2] with column of my keywords.

And now I want to compare all my keywords against my content and get a list of keywords with number of occurrences in the content. (clear enough?)

Answer 1

This would return you list of IDs from Table1 (id int, txt ntext) with key woards from Table2 (kwd nvarchar(255)) that exist in ntext field. Number of occurrences is tricky and you will have to write UDF, preferable CLR one, to get it.

I defined word as everything that is separated by space or open parenthesize from left and space, close parenhesize, comma, dot or semicolon from right. You can add more conditions eg quotes, double-quotes etc.

Select Table1.id, Table2.kwd 
  From Table1
 Cross Join Table2 
 Where patindex(N'%[ (]'+Table2.kwd+N'[ ,.;)]%',N' '+cast(Table1.txt as nvarchar(max))+N' ')>0
Order by id, kwd

Answer 2

What version of SQL Server? If SQL2008 you can do (probably after casting from text to nvarchar(max))

WITH Table1 AS
(
SELECT 1 AS Id, N'how now brown cow' AS txt UNION ALL
SELECT 2, N'she sells sea shells upon the sea shore' UNION ALL
SELECT 3, N'red lorry yellow lorry' UNION ALL
SELECT 4, N'the quick brown fox jumped over the lazy dog'
),
Table2 AS
(
SELECT 'lorry' as keyword UNION ALL
SELECT 'yellow' as keyword UNION ALL
SELECT 'brown' as keyword
)

SELECT Table1.id,display_term, COUNT(*) As Cnt
FROM Table1
CROSS APPLY sys.dm_fts_parser('"' + REPLACE(txt,'"','""') + '"', 1033, 0,0)
JOIN Table2 t2 ON t2.keyword=display_term
WHERE TXT IS NOT NULL
GROUP BY Table1.id,display_term
ORDER BY Cnt DESC

Returns

id          display_term                   Cnt
----------- ------------------------------ -----------
3           lorry                          2
3           yellow                         1
4           brown                          1
1           brown                          1