September 27, 2006 at 12:39 pm
Hello,
Two questions.
1) How do I find out if I have an exact match when doing a full text search?
2) Then if I get an exact match, how do I return only the exact match, and if I do not get an exact match, how do I return all the matches?
Thanks
John Plain
September 28, 2006 at 12:28 am
#1: You should use CONTAINS or CONTAINSTABLE and put the search word or search phrase in double quotes, for example:
select pub_id from pub_info where contains(pr_info,'"moon"')
will return only rows that contain "moon" as CONTAINS or CONTAINSTABLE predicates are boolean.
#2: Well, using the above example, all rows with contain the "exact match" to the search word "moon" will be returned. You have to define what you mean by "return only the exact match" better. You can limit the resuts using TOP 1 in the select list or if you're exact match is determied by relevancey (or by RANK) your can use the CONTAINSTABLE query and include RANK and ORDER by [RANK] and use TOP 1 to get the top "rank" or "only the exact match". If this is not what you're looking for, could you provide a more specific example?
Regards,
John
SQL Full Text Search Blog
http://jtkane.spaces.live.com/
John T. Kane
September 28, 2006 at 11:06 am
John,
Thanks so much for your reply. Here's a specific example.
We have a member called Patina Catering. With the full text index search, when you type Patina Catering into a search box (on an ASP page), all members with Catering in their business titles are returned. Patina Catering is also returned, but they are buried in the list of caterers. I need them to come to the top of the list (or be the only business returned in the list, since the words Patina Catering were typed into the search box). The value of the search box on the asp page is search.
I would like to apply this logic to all members, not just Patina Catering. Thanks for any additional help you can provide.
John Plain
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply