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.

  • I'm thinking the most likely approach for a resolution is dynamic SQL here to build out the query and another query to obtain the words from the user input with also dynamic SQL. – Shaulinator Feb 26 '18 at 16:27
  • @Shaulinator do you mind posting a solution with an example. Thanks. – Mike Feb 26 '18 at 17:09
  • it looks like you got the solution by using variables. I'm not quite sure on what you mean that "you're not sure of what words were found in the Table_A.UserInput". Do you mean the list of contents back from Table_A is correct, but you want to know for each row what the correlating variable AllWords values were that resulted in a returned row? – Shaulinator Feb 26 '18 at 17:13
  • Exactly. I have more than a thousand words in Table_B. I would like to know which one caused the row in Table_A to be selected. That is why I was trying the cross join first. – Mike Feb 26 '18 at 17:29
  • Hi @Mike, it depends on what you want to accomplish here but I would not try to make the whole process in a single query. If there is an application beneath, why not simply create a "static" table containing all the words that are prohibited, index these values. The application would check if user input contains these words and raise an error before going any further and actually use fulltext? – Jefferson B. Elias Feb 28 '18 at 6:31
  1. To reiterate, you currently have a table with a list of words you want to make sure your users are not searching Table_A with.

  2. You have successfully been able to query Table_A with Table_B which contains your list of bad words.

  3. Your end goal going from the first point, to the second point, is that you want to know if they searched for those bad words, what would be returned. What bad word correlates to a return of a record in Table_A.

What you need to do to resolve point 3 is query sys.dm_fts_parser. There is an excellent Example by Jefferson Elias on SQL Shack. I'll post the relevant bit in case it is removed or broken by the internet later:

How to check the results of a Full-Text parsing

There are two ways to check how Full-Text feature parses a given text depending on the source of the text.

Source of the text is a String

If you want to check fast what keywords you would get for a particular string, you might want to use sys.dm_fts_parser built-in function.

Here is an example of call to that function.

The first parameter is the string that has to be parsed. The second parameter is the language identifier. Here, it’s set to 0, which means it’s neutral. The hhird parameter is the identifier of the stoplist. Here no stoplist is used. The last parameter tells this function whether to be sensitive or not to accents. Here, we asked for insensitivity. In other words, this function will take the information you would provide when creating a Full-Text Index.

 select * from sys.dm_fts_parser(
    '" dsolkjfdskljfsd dfsd-MMM-236.127 dojfdslfkjds"',
) ;

If a table is already created with a Full-Text index, we would use another dynamic management function (DMF) called sys.dm_fts_index_keywords which takes as a parameter:

The database identifier in which it should look at The object identifier in that database It returns a dataset with a hexadecimal representation of the keyword, its corresponding form in the plain text, the identifier of the column in which the keyword has been found and finally the number of documents where this keyword can be found.

You will find below a T-SQL query to get back keywords found by Full-Text feature in our dbo.DM_OBJECT_FILE table so as its results set.

select * 
From sys.dm_fts_index_keywords(DB_ID(),OBJECT_ID('dbo.DM_OBJECT_FILE'))

This method should show you what would be returned based on your criteria. To help with point 1, you may want to look into Stopwords, Stoplists, and the Thesaurus features to help change the words from Table_B into usable words. Alternatively, you may need to implement triggers to keep the terms from being searched.

I imagine this code would work:

Declare @AllWords nvarchar(4000) = '';
From Table_B;
select * from sys.dm_fts_parser(
) ;

The answer from @Shaulinator is not exactly what I was looking for but he pointed me out to dynamic management views and functions (DMV, DMF) which I think can help to solve my problem. Thank you @Shaulinator.

In my solution, I'm going to use the function called


Microsoft's documentation: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-fts-index-keywords-by-document-transact-sql

In summary, this function provides a list of all the keywords (noise/stop words removed) found by full-text search index and also points back to the row that contains that keyword.

Proposed solution:

-- Table_A stores input from users and it has a full-text search inde x on UserInput
-- Table_A(ID, UserInput)
-- Table_B stores the words that users should not be using
-- Table_B(Word)

-- This query will return the entries in Table_A that contain words that should not be used
-- and what was the word that should not be used.
Select ID, UserInput, Word
From sys.dm_fts_index_keywords_by_document(DB_ID(), OBJECT_ID('Table_A')) As X
   Inner Join Table_B On X.display_term = Table_B.Word
   Inner Join Table_A On X.document_id = Table_A.ID

A word of advice, the performance of this query is not so good. In my scenario Table_A has 50K rows, the result from the sys.dm_fts_index_keywords_by_document function returns 300K rows, and Table_B has 1035 rows.

The query returns 11 rows and it takes an average of 1.8s to complete. How to improve this will vary depending on your environment and it is definitively a topic for a different question.

Your Answer

