Search code examples
htmlsqlsql-serverfilterfreetext

Apply HTML filter to FREETEXT SQL Query on nvarchar(max) column

So I'm converting an existing system to support Freetext search and I've a tiny issue. The column CONTENT is type of nvarchar(max) and stores HTML, which means cases like

<p><b>f</b>oo</p>

Will not work because the HTML Filter won't be applied. So I'm seeking a solution to manually force DB to use the HTML filter on the column during FREETEXT queries on that column

SELECT [ID],[content]   
FROM [dbo].[Core_Note]
WHERE FREETEXT([content], 'banana phone noodle foo');

So this query will return all matches except for the one outlined above.

Current output will be:

<p><br></p><p>Banana!<br></p>
Banana Phone

While should/desired be

<p><br></p><p>Banana!<br></p>
Banana Phone
<p><b>f</b>oo</p>
Solution

  • In the end there was no useful workaround that wouldn't ruin the purpose of FreeTextSearch so VARBINARY with HTML Filter was the answer.

    Details: Solution is achieved by creating triggers that will pass the relevant Freetext data in form of Varbinary to a FreeText Table and apply the relevant filters.