3

I'm using SQL file table and for instance I have a saved text file named "SOS.txt" which contains following text

For god's sake, save us right now please. We can't survive. Now or never!

Now I want to find all files that contain the word save, so I execute following query

SELECT * FROM FileTableExample
WHERE CONTAINS(file_stream, 'save')

and here's the result: The snapshot from result

stream file => 0x616C692053617665207573207269676874206E6F772E0D0A4E6F77206F72206E6576657221

As you can see I got the true result, the third column of the result indicates the file under name SOS.txt, I have the stream_id and stream_file but what I'm about to find is the way to show the the intended text in company with it's surrounding in human readable format. Somethings like this:

   Name      |     Excerpt
-------------+----------------------
 SOS.txt     |..sake, save us..

Is there any way?

Update:

After searching on the net I found this article which is useful but it didn't mention about full text search in filetable structure.

Based on this article, I converted file stream to string:

SELECT CONVERT(varchar(MAX), file_stream) AS Excerpt, *
from FileTableExample
where contains(file_stream, 'save')

enter image description here

It works if the file is a plain text like SOS.txt but if it's .docx or .pptx file, you are not going to gain a useful convention.

Muhammad Musavi
  • 2,512
  • 2
  • 22
  • 35

1 Answers1

0

Use this, CAST(file_Stream as varchar(max))

lokesh
  • 85
  • 1
  • 9
  • The problem still exists, it doesn't return true conversion when the file is an office document or PDF file. In my case the conversion result was PK + 3 weird characters. – Muhammad Musavi Aug 15 '18 at 04:06