Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Professional SQL Server 2005 Programming ISBN: 0-7645-8434-0
This is the forum to discuss the Wrox book Professional SQL Server 2005 Programming by Robert Vieira; ISBN: 9780764584343
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional SQL Server 2005 Programming ISBN: 0-7645-8434-0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old December 29th, 2006, 02:40 PM
Authorized User
Join Date: Mar 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 1 Time in 1 Post
Default FTS - using 'NEAR' across multiple columns


Firstly, thanks to Rob for another great book which picks up where the 'Beginning' version left off. It is a great learning resource and then an excellent reference.

I have been using Chapter 21 on Full-Text Search quite extensively. I am using 'CONTAINSTABLE' on a single table (well, actually a view but I don't think that makes any difference) which has two Full-Text-Indexed columns.

I am using a Stored Procedure for parsing search-terms, which can be found here: http://www.sqlservercentral.com/colu...ingroutine.asp

It converts a search term such as 'apple fruit' into:

"apple fruit" OR ("apple" NEAR "fruit") OR ("apple*" NEAR "fruit*") OR (FORMSOF(INFLECTIONAL,"apple") AND FORMSOF(INFLECTIONAL,"fruit"))

This search string works very well with CONTAINSTABLE, as long as both search terms are in the same column.

However, if the two search terms are in different columns, it is not so successful.

For example, suppose we have two full-text-indexed columns in the same table, called 'FoodName' and 'FoodType'.
There is a record in the table with 'Foodname' of 'Apple' and 'FoodType' of 'Fruit'.
The search string above will not find this record, because the 'NEAR' function only seems to work on terms within the same field, and does not seem to work across fields in different columns of the same record.

Is there any way of using 'NEAR' across more than one column? I would imagine it is a fairly common requirement but I can't think of any straightforward way to do it. I could create a trigger which updates an additional 'Search' field that is a concatenation of 'Foodname' and 'Foodtype' ('Apple Fruit', for example) and then do Full-Text-Index searches on this. But I'm sure there is a better way to do it.

Any thoughts much appreciated.
Reply With Quote
  #2 (permalink)  
Old December 29th, 2006, 05:15 PM
Authorized User
Join Date: Mar 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 1 Time in 1 Post

Right... it turns out that FREETEXTTABLE will search across columns whereas CONTAINSTABLE does not. To use the example previously, with FREETEXTTABLE a search on 'apple fruit' gives the record with both these words in it, even though they are each in different columns.
You do not need to use the 'NEAR' keyword (indeed, you cannot use it with FREETEXTTABLE).

Searches on 'apple' or 'fruit' give the same result.

However, with FREETEXTTABLE I am unable to use wildcards (i.e. '*').
A search on 'app' or 'fru' gives no records. Using the CONTAINSTABLE method above, it would result in the correct record.

All things considered, I think I will just use FREETEXTTABLE as on balance for this situation it solves a bigger problem than it creates.
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple checkbox columns mariag BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 0 June 4th, 2007 03:06 PM
Updating Multiple columns from subquery r_ganesh76 SQL Server 2000 2 December 9th, 2004 11:39 PM
displaying multiple fields in columns rbegonia Classic ASP Basics 3 July 31st, 2004 08:37 PM
Multiple Columns in an Index owain SQL Server 2000 8 June 17th, 2003 03:44 AM
Multiple Columns in an Index owain Access 2 June 16th, 2003 12:15 PM

All times are GMT -4. The time now is 05:19 PM.

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.