Search keyword in Documents stored in file system from SQL Server

Jagdish Devaku
Jagdish Devaku used Ask the Experts™
on
Hi,

Can you please help me in searching keywords with in the documents stored in file system from SQL Server.

I do remember that I used to do this using full text by using the path of the document, file name and keyword in single query. I could not get the exact way.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi
must be possible with MS and its indexing.
if you have a Linux client you can use you can try "
was going to type it out but here this will at least explain it :) "
i'll have a scout in windows, I have a feeling you might need to enable advanced searching and indexing if on a server but that will add load.
Jagdish DevakuSr DB Architect

Author

Commented:
Hi Stolsie,

Thanks for the response.

We are using windows & sql server.

And I want to search a string with in the document from sql query.

Thanks.

Commented:
ahh sorry I read it as you wanted to search a document for a specific line.. oops...
What I know about SQL is dangerous, I'll bow out, sorry.
I'll leave you with this click me :)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Raju SrivatsavayeSoftware Engineer

Commented:
I vaguely remember something like this a while back.

https://www.mssqltips.com/sqlservertip/2769/sql-server-semantic-search-to-find-text-in-external-files/

Hope it helps
Jagdish DevakuSr DB Architect

Author

Commented:
@srivatsavaye - I already checked this.

But looks like I don't have any option than to try this.

I will keep you posted once I try this.

Thanks.
Jagdish DevakuSr DB Architect

Author

Commented:
I forgot that I am using SQL Server 2008 R2.

I think  Semantic Search will work from SQL Server 2012.
Raju SrivatsavayeSoftware Engineer

Commented:
Bummer!! I didn't realize you are using 2008 R2. I don't think i can help you with this. Sorry.

Good luck.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
You'll need to use the FULL TEXT search feature.
Jagdish DevakuSr DB Architect

Author

Commented:
Hi vitor,

Documents are stored in the file system and not in the database. Can you please help me.

Thanks.
IT Engineer
Distinguished Expert 2017
Commented:
Can you please help me in searching keywords with in the documents stored in file system from SQL Server.
This means you're using FILESTREAM?
If so then you'll just need to create a fulltext index on the column that stores the document in the table and then use the FULL TEXT search functions. For example:
SELECT *
FROM  TableName
WHERE  CONTAINS(filestream_ColName,'my test')
Jagdish DevakuSr DB Architect

Author

Commented:
Hi Vitor,

I am on a vacation and could not respond.

Documents are stored in file system and not in the column.

Is there a way to do the below.

Loading the documents from file system to the tables using query and then index the column as you said above.

Thanks.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Hi Jagdish,

Sorry to get back to this question just now.

Did you read the link I post in my previous comment. From there you can jump to other pages that has everything you need.
Anyway here's a good article with examples how to work with FILESTREAM.
Jagdish DevakuSr DB Architect

Author

Commented:
Thanks Vitor.

I am going through it.
Jagdish DevakuSr DB Architect

Author

Commented:
Finally I have done it by enabling FileStream and created a database with file Stream datafile.

 Created a table & added a full text on the table.

Thanks all for your help.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I'm very glad to hear that.
Cheers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial