Does anyone have a recommendation on how to best handle searchable documents (such as MS Word (.doc/.docx), MS Excel (.xls/.xlsx), Email (.msg,.eml), Searchable PDF) and non-searchable documents (such as non-searchable .pdf, .jpg/.jpeg, .tiff, .png) when wanting to use Freetext in SQL Server?
I can use the IFilters for office, .pdf and I think .tiff should also work and potentially build or buy extra ones.
The non-searchable would require OCR to be applied on them but I don’t like the suggestions I’ve got so far which are:
-
Store both searchable content of all documents as text in an column called SearchableText for example. Problem with that is that it’s extra work having to extract the data from already searchable documents and it’s duplicating the data.
-
Store the ocr result of the non searchable documents in a column called OCRText and then apply a computer column (SearchableContent for example) and use the Document column when it is a searchable document otherwise use the OCRText when non-searchable. This would get rid of the extra work of having to extract the text data from already searchable document and get rid of the duplication mentioned in the point above, but problem with that one is:
SQL complains that my query is non-deterministic or not precise enough if I use a function in my compute column to checks a table to see if documents are non-searchable as I didn’t want to hardcode the document type in a “select case” in the event I needed to add new one i.e. .bmp for example.
I read that I could make the compute column “persisted” but I’m not sure what this entails as this point and will research it further.
Does anyone have a better suggestion?
I would probably go for solution 1 but this means that I need IFilters for all document types rather than just the non-searchable one which is not great but solution 2) would provide more flexibility and less work in terms of what has to be processed but clearly can’t hardcode document types.