June 13, 2011 at 8:03 pm
I am working on a SQL Server 2008R2 query that leverages full text searching to find the appropriate rows with the data. However, and here is where I am stuck, I also have the requirement of providing which terms/stemmers matched when the terms were evaluated using OR with CONTAINS() or CONTAINSTABLE(). However, I cannot seem to find or come up with a way to produce the needed results.
Example query to find associated records:
SELECT
documentId,
content
FROM dbo.tbl_Documents d
INNER JOIN CONTAINSTABLE(tbl_Documents, contents, '"*West*" OR "*Consent*" OR "*Test*"') AS filter
ON d.documentId = filter.[Key]
This returns:
documentId content
7 West of the Mississippi
10 West today, I consent.
13 West yesterday, east today, let us test.
However, I need something like this:
documentId content matchSet
7 West of the Mississippi west
10 West today, I consent. west, consent
13 West yesterday, east today, let us test. west, test
I could not find a way to know what stemmer was found and the the only way I can figure out how to know even which term was found is by evaluating each term separately and looping through them or possibly by doing some joins with CONTAINS as a predicate.
Has anyone else had a similar requirement with full-text searching and if so, how did you go about solving that requirement?
I'm up for any suggestions or resources!
I've been digging through MSDN but I don't see anything. Some articles I've reviewed:
Full-Text Predicates and Functions Overview
Full-Text Catalog and Index How-to Topics (Full-Text Search)
This question was originally posted on ask.sqlservercentral.com.
Thank you!
Michael
June 14, 2011 at 12:28 pm
I am working on a SQL Server 2008R2 query that leverages full text searching to find the appropriate rows with the data. However, and here is where I am stuck, I also have the requirement of providing which terms/stemmers matched when the terms were evaluated using OR with CONTAINS() or CONTAINSTABLE(). However, I cannot seem to find or come up with a way to produce the needed results.
Example query to find associated records:
SELECTdocumentId,
content
FROM dbo.tbl_Documents d
INNER JOIN CONTAINSTABLE(tbl_Documents, contents, '"*West*" OR "*Consent*" OR "*Test*"') AS filter
ON d.documentId = filter.[Key]
This returns:
documentId content
7 West of the Mississippi
10 West today, I consent.
13 West yesterday, east today, let us test.
However, I need something like this:
documentId content matchSet
7 West of the Mississippi west
10 West today, I consent. west, consent
13 West yesterday, east today, let us test. west, test
I could not find a way to know what stemmer was found and the the only way I can figure out how to know even which term was found is by evaluating each term separately and looping through them or possibly by doing some joins with CONTAINS as a predicate.
Has anyone else had a similar requirement with full-text searching and if so, how did you go about solving that requirement?
I'm up for any suggestions or resources!
I've been digging through MSDN but I don't see anything. Some articles I've reviewed:
Full-Text Predicates and Functions Overview
Full-Text Catalog and Index How-to Topics (Full-Text Search)
CONTAINSTABLE (Transact-SQL)
CONTAINS (Transact-SQL)
This question was originally posted on ask.sqlservercentral.com.
Thank you!
Michael
Michael
You can try something of this nature:
with w as (select documentid, 'west' as matchset from tbl_Documents where content like ('%west%'))
, c as (select documentid, 'consent' as matchset1 from tbl_Documents where content like ('%Consent%'))
, t as (select documentid, 'test' as matchset2 from tbl_Documents where content like ('%test%'))
SELECT d.documentId, d.content, w.matchset, c.matchset1 , t.matchset2
FROM tbl_Documents d
INNER JOIN
CONTAINSTABLE(tbl_Documents, content, '"*West*" OR "*Consent*" OR "*Test*"') AS f
ON d.documentId = f.[Key] left join w on d.documentid = w.documentid
left join c on d.documentid = c.documentid
left join t on d.documentid = t.documentid
Hopefully that helps.
"There are no problems! Only solutions that have yet to be discovered!" :w00t:
June 20, 2011 at 6:04 pm
bopeavy,
Thanks for looking this over and responding!
After some testing, we decided to go with a stored procedure that runs a query to find the documents as well as using an inline function that returns which term was found. We'll see how that goes. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply