Is there a way to do a full-text search using words stored in another table? This is my scenario:
- Table_A contains phrases entered by users in field "UserInput" (and other stuff). I have a full-text index on this table looking at UserInput.
- Table_B with one field "Word" containing a bunch (more than a 1k) words that I want to make sure my users are not using.
I tried this query:
Select * From Table_A Cross Join Table_B Where Contains(UserInput, Word)
But that does not work. The error says "Incorrect syntax near 'Word'". SSMS also complains saying "Expecting STRING, TEXT_LEX or VARIABLE".
I have also tried CHARINDEX and LIKE in the WHERE statement but those functions return false positives as they do not look for whole words but strings within strings.
If full-text will not work, is there any other approach that can help identify whole words from Table_B.Word in Table_A.UserInput?
Also tried the following:
Declare @AllWords nvarchar(4000) = ''; Select @AllWords = @AllWords + Case When @AllWords = '' Then Word Else ' OR ' + Word End From Table_B; Set @AllWords = '''' + @AllWords + ''''; Select * From Table_A Where Contains(UserInput, @AllWords);
This one seems to bring up the right results but I don't have a way to know what words were found in Table_A.UserInput.