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.
| Author |
Topic |
|
ctmorse
Starting Member
2 Posts |
 Posted - 2005-03-23 : 11:51:58
|
| Hello All,I want to run a full-text query against all the indexed fields of a given table to find all of the given terms using CONTAINS. Say that my table contains a name field and a spouse field. If I tried, for example, using CONTAINS(*, 'marge and homer simpson') to search the table and a particular record had 'homer simpson' in the name field and 'marge' in the spouse field, I would want this record returned. Howerver, the way CONTAINS(* seems to work is that it wants to find all the terms in any single field, not across all fields.I can think of two solutions but neither of them is great. One solution would be to create a new table with a single text field that contained all the text from all the fields in question and run a query against that. My database is not that big so the amount of data would not be an issue but making sure that the 'all text' table was always updated seems like a bit of a pain.Another solution would be to create my query by breaking up the search terms. Following the example above it would look like WHERE CONTAINS(*, 'marge') AND CONTAINS(*, 'and') AND CONTAINS(*, 'homer') AND CONTAINS(*, 'simpson'). However this would generate an error because CONTAINS(*, 'and') would not have any non-noise words. So I would have to compare all the individual terms against the noise words list and remove them all client side...again, this seems like more work than necessary.Does anyone know of a better way to search across all fields for all the given terms?Thanks,Chris |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-03-23 : 12:41:25
|
| Contains((fielda + ' ' + fieldb) , 'marge homer')JimUsers <> Logic |
 |
|
|
ctmorse
Starting Member
2 Posts |
Posted - 2005-03-23 : 13:37:07
|
| Hi Jim. Thanks for the response. However, this doesn't seem to work. I get a syntax error when trying to include two field names as shown. I also tried several variations without any luck.Also, 'marge homer' doesn't seem to be valid, at least in SQL Server 2000. I can only get it to work if I put 'and' or 'or', etc inbetween works in the search term.Chris |
|
|
|
|
|
|
|
|
FullText Query for All Terms Across Indexed Fields