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:
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)
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy