Quoted literal strings won't force a phrase match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Quoted literal strings won't force a phrase match

Author

Topic

Afroblanca
Starting Member

9 Posts

Posted - 2008-01-18 : 12:36:15

Hello all,

From what I've read, SQL Server is supposed to do a phrase match when you do a full text search that contains quoted literal strings. So, for example, if I did a full text search on the phrase "time out" and I put it in quotes, it's supposed to search for the full phrase "time out" and not just look for rows that contain the words "time" or "out." However, this isn't working for me.

Here is the query that I'm using :

SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC

What's it's doing is this : it's returning a bunch of rows that have the words "time" or "out" in the column called hed. It's also returning rows that have the full phrase "time out", but it's giving those rows the same rank as rows that only contain the word "time." In this case, that rank is 180.

Is there anything else I should be doing in my query, or is there some configuration option I should have turned on?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 12:49:07

Try using CONTAINSTABLE() with NEAR option

Afroblanca
Starting Member

9 Posts

Posted - 2008-01-18 : 13:05:19

doesn't seem to work. I changed the query to this :

SELECT *
FROM Content_Items ci
INNER JOIN CONTAINSTABLE(Content_Items, itemcontent, 'time NEAR out') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC

And this time around I didn't get any results at all.

Afroblanca
Starting Member

9 Posts

Posted - 2008-01-18 : 13:08:24

I also ran this query, which didn't return any results :

SELECT *
FROM Content_Items ci
INNER JOIN CONTAINSTABLE(Content_Items, hed, 'time NEAR out') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC

Afroblanca
Starting Member

9 Posts

Posted - 2008-01-18 : 15:45:27

Ok, I've made some progress on this problem. Apparently SQL Server is ignoring noise words in my phrase match.

For example, I ran this query :

SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time capsule"') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC

And it did exactly what it was supposed to do, since neither "time" nor "capsule" is a noise word.

My impression was that noise words aren't stripped out of a full text search if the search phrase is a quoted literal. Thus, my search for "time out" should look for the full phrase "time out", and not just the word "time."

Does anybody know why SQL Server is removing my noise word from the phrase match?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources