correlated query contains not working

boonchoo chatsrinopkun 0 Reputation points
2023-03-30T09:28:52.6466667+00:00
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'.
SQL Server | Other
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 47,611 Reputation points
    2023-03-31T10:12:07.0333333+00:00

    e.g. '"*endwith"

    FTS don't support prefix wildcard search; you don't get it working.

    0 comments No comments

  2. Viorel 126.3K Reputation points
    2023-03-30T10:51:59.35+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.