September 14, 2007 at 5:45 am
Hi,
I've created a full-text index and the searches are really faster, but I have the following problem:
Suppose that I want to find the word 'server' in a field that contains 'sqlserver2000'. Before creating the index my search was 'FIELDNAME LIKE '%server%' and it returned all the results correctly. With full-text index I use the search 'CONTAINS(FIELDNAME,'"server"')' and it returns no results!
I've tried with 'CONTAINS(FIELDNAME,'"*server*"')' but it doesn't work.
What I'm doing wrong?
Thanks
September 14, 2007 at 6:39 am
As far as I know, you simply can't use this type of condition. The asterisk can only be at the end of the phrase when using CONTAINS.
BOL seems to confirm this:
The CONTAINS predicate supports complex syntax to search character-based columns for:
- One or more specific words and/or phrases (simple term).
A word is one or more characters without spaces or punctuation. A valid phrase can consist of multiple words with spaces with or without punctuation between them. For example, croissant is a word, and café au lait is a phrase. Words and phrases such as these are called simple terms.
- Inflectional form of a specific word (generation term).
- A word or a phrase where the words begin with specified text (prefix term).
In case of a phrase, each word within the phrase is considered to be a prefix. For example, the term auto tran* matches automatic transmission and automobile transducer.
- Words or phrases using weighted values (weighted term).
- A word or phrase close to another word or phrase (proximity term).
No sign that you would be allowed to use wildcard at the beginning.
CONTAINS(FIELDNAME,'"server"') should work only if the "server" is a standalone word... i.e., "My server is down" should be found, but "stop_server_flag" or "sqlserver2000" should not.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply