SQL Server - full text search sentences that match keyword
-
I am using full text search to search through PDF documents using the Adobe iFilter. Everything works fine. Now, is it possible that I get a sentence which contains my searched keyword? For example: Keyword: 'fox' Query result: 'The quick brown fox jumps over the lazy dog.' 'Fox is a small red animal.'
-
I am using full text search to search through PDF documents using the Adobe iFilter. Everything works fine. Now, is it possible that I get a sentence which contains my searched keyword? For example: Keyword: 'fox' Query result: 'The quick brown fox jumps over the lazy dog.' 'Fox is a small red animal.'
Member 8024623 wrote:
Now, is it possible that I get a sentence which contains my searched keyword?
Ehr.. yes, especially since you already mentioned that it works. Did you give it a try? I got the feeling that I'm misunderstanding your question. English isn't my native tongue, and it helps if there's a bit explanation and some code to give an indication of what is expected. That said, kudo's for including the example with the expected output :)
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
Member 8024623 wrote:
Now, is it possible that I get a sentence which contains my searched keyword?
Ehr.. yes, especially since you already mentioned that it works. Did you give it a try? I got the feeling that I'm misunderstanding your question. English isn't my native tongue, and it helps if there's a bit explanation and some code to give an indication of what is expected. That said, kudo's for including the example with the expected output :)
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
I also think you've misunderstood my question :) When I said that everything works fine I meant that full text search over PDF documents works. I can retrieve a PDF document that contains a searched keyword. But, I need to retrieve a sentence or sentences from that or any other document that contain that keyword.
-
I also think you've misunderstood my question :) When I said that everything works fine I meant that full text search over PDF documents works. I can retrieve a PDF document that contains a searched keyword. But, I need to retrieve a sentence or sentences from that or any other document that contain that keyword.
asimptota777 wrote:
But, I need to retrieve a sentence or sentences from that or any other document that contain that keyword.
Then you will need
IFilters for each type of document that's in your database. There's no way of reading "every" document, since each fileformat (and their versions) have different encodings and layouts. You can download those for Office here[^] (2007/2010). If you have a document in there in your own file-format (binary serialized data?) you'd probably have to provide your own IFilter implementation (guidelines on MSDN). All existing and wide-used formats should have an implementation, since the same IFilter is in use for Desktop Search, SharePoint and the likes. And of course, CodeProject[^] has a lot of articles on the subject.Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
asimptota777 wrote:
But, I need to retrieve a sentence or sentences from that or any other document that contain that keyword.
Then you will need
IFilters for each type of document that's in your database. There's no way of reading "every" document, since each fileformat (and their versions) have different encodings and layouts. You can download those for Office here[^] (2007/2010). If you have a document in there in your own file-format (binary serialized data?) you'd probably have to provide your own IFilter implementation (guidelines on MSDN). All existing and wide-used formats should have an implementation, since the same IFilter is in use for Desktop Search, SharePoint and the likes. And of course, CodeProject[^] has a lot of articles on the subject.Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
Thank you for an answer but I think you haven't read my question carefully. In my first post I said that I store PDF documents in the database (in the Filestream to be precise)and I use Adobe iFilter for full text search through PDF documents. That works fine. What I need is a way to get sentences from those PDF documents that contain a certain keyword. What T-SQL syntax can I use to extract sentences that contain a certain word?
-
Thank you for an answer but I think you haven't read my question carefully. In my first post I said that I store PDF documents in the database (in the Filestream to be precise)and I use Adobe iFilter for full text search through PDF documents. That works fine. What I need is a way to get sentences from those PDF documents that contain a certain keyword. What T-SQL syntax can I use to extract sentences that contain a certain word?
asimptota777 wrote:
What T-SQL syntax can I use to extract sentences that contain a certain word?
USE AdventureWorks2012;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, @SearchWord);Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
asimptota777 wrote:
What T-SQL syntax can I use to extract sentences that contain a certain word?
USE AdventureWorks2012;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, @SearchWord);Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
Ok, I know about FREETEXT. But with this command you get THE WHOLE Description field. I need to get only the sentence that contains a keyword. Query: SELECT Description FROM Production.ProductDescription WHERE FREETEXT(Description, 'smooth'); Result: 1. Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride. 2. Top-of-the-line competition mountain bike. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain. 3. Aerodynamic rims for smooth riding. 4. Excellent aerodynamic rims guarantee a smooth ride. I need result like this: 1. Smooth-shifting with a comfortable ride. 2. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain. 3. Aerodynamic rims for smooth riding. 4. Excellent aerodynamic rims guarantee a smooth ride.
-
Ok, I know about FREETEXT. But with this command you get THE WHOLE Description field. I need to get only the sentence that contains a keyword. Query: SELECT Description FROM Production.ProductDescription WHERE FREETEXT(Description, 'smooth'); Result: 1. Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride. 2. Top-of-the-line competition mountain bike. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain. 3. Aerodynamic rims for smooth riding. 4. Excellent aerodynamic rims guarantee a smooth ride. I need result like this: 1. Smooth-shifting with a comfortable ride. 2. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain. 3. Aerodynamic rims for smooth riding. 4. Excellent aerodynamic rims guarantee a smooth ride.
asimptota777 wrote:
Ok, I know about FREETEXT. But with this command you get THE WHOLE Description field. I need to get only the sentence that contains a keyword.
..it doesn't work that way; the document is returned, as it could contain the searched word more than once, in multiple locations. You can easily write some code to find the sentence with the word that was searched and extract it.
asimptota777 wrote:
Result:
That's a list of requirements, not a programming question.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
asimptota777 wrote:
Ok, I know about FREETEXT. But with this command you get THE WHOLE Description field. I need to get only the sentence that contains a keyword.
..it doesn't work that way; the document is returned, as it could contain the searched word more than once, in multiple locations. You can easily write some code to find the sentence with the word that was searched and extract it.
asimptota777 wrote:
Result:
That's a list of requirements, not a programming question.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
Dude, I know what full text search is and how it functions. I just don't know how to implement the functionality I've mentioned. I must admit that your signature really suits you...
-
Dude, I know what full text search is and how it functions. I just don't know how to implement the functionality I've mentioned. I must admit that your signature really suits you...
asimptota777 wrote:
I must admit that your signature really suits you...
Thank you - it was hard to earn that title :-D You could repost the question with a link to this thread. State in the new post that this wasn't helpful, and someone might come up with something better.
asimptota777 wrote:
Dude, I know what full text search is and how it functions. I just don't know how to implement the functionality I've mentioned.
I did not look at the requirements; technically, you want to retrieve documents based on a searchterm. You get a list of documents that contain that term. You can retrieve the document. What's keeping you from doing a substring on that document and parse out the line? You can fetch the index where the word is in the document, then you work your way back to the first word with a capital letter, and forward to the first interpunction.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]