e.g. '"*endwith"
FTS don't support prefix wildcard search; you don't get it working.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
1) the rules table stores complex terms for FTS (e.g. '"*endwith" and "startwith*" or (...)')
2) the documents table stores contents of user's documents
3) create a view to display all matches between rules.keywordTerms and documents.documentTexts
create view vuRulesMatchedToDocumentTexts
as
select r.keywordTerms, d.documentTexts
from rules r
cross apply
(
select documentID, documentTexts
from documents
where contains(documentTexts,r.keywordTerms)
) d
go
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'r'.
e.g. '"*endwith"
FTS don't support prefix wildcard search; you don't get it working.
It seems that the second parameter of CONTAINS does not allow this. As alternative, probably you can define a stored procedure and use a cursor.
Or perhaps you can define a function like this:
create function vuRulesMatchedToDocumentTexts( )
returns @t table (documentID int, documentText nvarchar(max))
as
begin
declare @all_rules nvarchar(2000) = (select string_agg(keywordTerms, ' OR ') from rules)
insert into @t
select documentID, documentTexts
from documents
where contains(documentTexts, @all_rules)
return
end
Usage:
select * from vuRulesMatchedToDocumentTexts()
If you are interested in a stored procedure that uses cursors or other alternatives, then show details about your tables (with sample rows) and the expected output, and the version of SQL Server.