The database of my application stores files in a FILESTRAM blob field. I have a dedicated table where i keep all files.
THis is the table (DOCUMENT is the blob field):
CREATE TABLE [dbo].[DOC_FILES](
[ID_DOC_FILE] [int] NOT NULL,
[DOCUMENT] [varbinary](max) FILESTREAM NULL,
[GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[FILE_SIZE] [int] NULL,
CONSTRAINT [PK_DOC_FILES] PRIMARY KEY CLUSTERED
(
[ID_DOC_FILE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] FILESTREAM_ON [MyDatabase],
UNIQUE NONCLUSTERED
(
[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [MyDatabase]
What i'd like to perform is a search inside the files.
I would like to target files as pdf, doc, docx, rtf, txt.
Is it possible to do a query like this:
SELECT ID_DOC_FILES FROM DOC_FILES
WHERE DOCUMENT *CONTAINS* 'my search string' //*CONTAINS* of course is just for demonstration...
??
it should return all the IDs of the pfd, doc, docx, rt, txt files containing 'my search string'.
A workaround could be to create an INDEXED_CONTENT field where to save as text the content of the file (i can process one by one all the files to extract text).
Anyway before doing this I would like to be sure that searching inside blobs it is not possible.
Thank you.
