SQL Server FTS: Can I get information on how / why rows were matched?

Question

Is it possible to get information about why / how the string returned by the FTS request was matched (or which substring caused the string to match)? For example, consider a simple table with id and text columns, with an FTS index at a later date.

 SELECT * FROM Example WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, jump)'); 

This sample query may return, for example, the string {1, 'Jumping Jack'} .

Now, is it possible to somehow get information that this line was matched due to the word 'Jumping' ? It doesn't even have to be exact information, more that substrings made the string match .

Why I ask - I got a C # application that creates these queries based on user input (search keywords), and I need the most basic information about why / how the string was matched for further use in C code #,

If this is not possible, any alternatives?

---

EDIT regarding the answers of Mike Burton and LesterDove:

The above example was trivial for obvious reasons, and your decisions are fine, considering this, however, FTS queries can return results when a regular expression or simple string matching (like LIKE ) won't cut. Consider:

Search bind returns bound (past form).
A search for extraordinary returns amazing (synonym).

Both are valid matches.

I searched for solutions to this problem and found this: NHunspell . However, I already have FTS and valid results using SQL Server, duplicating a similar mechanism (creating additional indexes, saving additional words / thesaurus files, etc.) does not look very good.

Lester's answer, however, gave me some ideas that maybe I could split the source row into a temporary table and run the original FTS query for this split result. Since this may work for my case (where the DB is quite small and the queries are not very complex), in general, this approach is out of the question.

Answer 1

1 / Use the SPLIT function (many options may be Googled) on the original substring, which dumps individual substrings to some temp table with one row in a fragment of the substring.

2 / EDIT: you need to use CROSS APPLY to join the table-aware function:

 SELECT * FROM Example E CROSS APPLY Split(E.text, ' ') AS S WHERE CONTAINS(E.text, 'FORMSOF(INFLECTIONAL, jump)') AND S.String LIKE '%jump%'; 

* NOTE: You need to use forage for your own custom Split function. I used this one and applied the first comment editing to use whitespace as a delimiter.

So E is your example table. You are still viewing the FT in the text box for the word "jump". And now you "join" the table, consisting of the individual substring values ​​of your text field. Finally, you map this to the word “jump” using LIKE or Instr .

Answer 2

One simple post-processing method was to generate an equivalent regular expression for each article in the WHERE clause and use it to detect after the data found matches the specified pattern.

Answer 3

You can get SQL to tell you how it interpreted your query, including how it converted your input.

 SELECT occurrence, special_term, display_term, expansion_type, source_term FROM sys.dm_fts_parser('FORMSOF(INFLECTIONAL, bind)', 1033, 0, 0) 

returns

 occurrence special_term display_term expansion_type source_term 1 Exact Match binds 2 bind 1 Exact Match binding 2 bind 1 Exact Match bound 2 bind 1 Exact Match bind 0 bind 

This is not what you requested, but it is a beginning. You can search for results for anything in the display_term column and probably find out why it matches.