This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 73%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAA%3C/text%3E%3C/svg%3E)
I have a table with varbinary datatype (file stream enabled) column which store pdf files as binary , form that i can get result as which of file have 'invoice' word using following query.
SELECT * FROM FileStore WHERE CONTAINS(Document,'invoice')
Here "Document" is varbinary column which store pdf and word files as binary.
From this i want to get words which appear how many times, i mean number of occurrences.
E.G: In one of the pdf got 'invoice' word 5 times , so want to count how many time word appear in that table using query,
Note: i am asking in varbinay datatype, not for nvarchar datatype.
Are you sure that CONTAINS and other SQL functions will find the words in PDF and Word files, which have a specific encoded and compressed formats that are not supported in SQL?
Yes, this is supported by full-text. This is what the filters are for.
Yes as we enable full text index can filter with contains in varbinary datatype
There are a couple DMVs for fulltext that can help you. I think you should start with sys.dm_fts_index_keywords_by_document .
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
SQL Server does not have a function which counts the strings in full text indexes. It only identifies they exist or do not exist in the document.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 17%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EI1%3C/text%3E%3C/svg%3E)
This can be done, but it’s not straight-forward because SQL Server doesn’t have a specific function to do it.
I refer to this thread,hope it will help you :https://sql-bits.com/sql-server-how-to-count-occurrences-of-a-substring-in-a-string/
Best Regards,
Isabella
Hi, actually i asked for binary format not for string, i mention in description it for varbinary not for nvarchar. Yes for string can already know.
So have you looked at [sys.dm_fts_index_keywords_by_document[(https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-fts-index-keywords-by-document-transact-sql?WT.mc_id=DP-MVP-5440) yet? I believe this DMV have the answer you are looking for.
No , this is not, in that link they mention how to configure fulltext index and so on, i asked, how to get number of occurrences word appear in binary format.
No , this is not, in that link they mention how to configure fulltext index and so on,
No, not in the link I was pointing to. Seems that you did not read it very carefully. Anyway attached is a demo. And, no, in this demo there is no binary data, but that shouldn't matter. You are querying the fulltext index, not the binary raw data. (I could include the code directly in the post, because apparently, I triggered a spam filter.)
I am querying from binary data which store in varbinary(max) datatype column. from nvarchar can get already know, form your txt file , it will count occurrences from nvarchar datatype and not from varbinary datatype.
What results or errors did you get using sys.dm_fts_index_keywords_by_document with your table and catalog?
Sorry, I don't get this. You have something, for instance PDFs, that are stored as varbinary(MAX). You have a fulltext index on that column. The fulltext index has the words in the documents. You want to know how many times the word "invoice" occurs in one or more documents. I'm claiming that sys.dm_fts_index_keywords_by_document can answer that question for you.
Did you try it? If so, what did you not like the result.
Again, the data type of the column has nothing to with it, once you have the fulltext index in place.
Just to expand on Erland's answer.
You are not searching the varbinary when you use CONTAINS. You are searching the FULL TEXT index for words. This has nothing to do with the source which populated the full text index.
sys.dm_fts_index_keywords_by_document is a full text index view.