0

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>
VoidEssy
  • 23
  • 7
  • I think you would have to strip out all the HTML when doing your comparison. This is because the string section "foo" does not match "foo". There are some nasty functions out there to strip HTML but they are not pretty or fast. – Sean Lange Feb 12 '20 at 14:45
  • Yeah that would ruin the purpose of running FREETEXT if it's at the cost of speed. So don't think it's an option to strip it out. – VoidEssy Feb 12 '20 at 14:54
  • Right that was pretty much my point. I don't know of a way to have freetext ignore markup characters. Maybe you can store a plain text copy in another column to use for searching? Not sure how much data you have as that could eat up a lot of disc space but it would keep things fast. – Sean Lange Feb 12 '20 at 15:39
  • Oh yeah, well lets see if maybe there are any wizards around who know a trick else I think it will be far more reasonable and straightforward to put up an ultimatum with "Varbinary with HTML conversion or no Freetext for you with HTML Filtering" – VoidEssy Feb 12 '20 at 15:43

1 Answers1

0

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.

VoidEssy
  • 23
  • 7