Contains problem

Hi,
B4 i ask a question about the contains(myfield, '"the apple*"') problem
if i'm using contains(myfield, '"the apple*"'), it will find -> theta apple
however, my case is sometimes i need to use the *, but sometimes i dun want
search some unrelated words
for example :
user type in "the water" -> i need to find all prefix is water's words such
as "the waterfall"
but i dun want search all unrelated record such as "theta water...", then
how can i handle this problem ?
Thank you for your help!
Q wrote on Sat, 21 May 2005 09:46:51 +0800:

> Hi,
> B4 i ask a question about the contains(myfield, '"the apple*"') problem
> if i'm using contains(myfield, '"the apple*"'), it will find -> theta
> apple however, my case is sometimes i need to use the *, but sometimes i
> dun want search some unrelated words
> for example :
> user type in "the water" -> i need to find all prefix is water's words
> such as "the waterfall"
> but i dun want search all unrelated record such as "theta water...", then
> how can i handle this problem ?
> Thank you for your help!
You'll need to split your clause up, eg.
contains(myfield,'the') and contains(myfield,'"apple*"')
Dan
|||one more question
i encounter the case
when i want to search "00001234" <-- this no.
my SQL is like this CONTAINS(myfield, '"1234*"')
however, it cannot locate the record
If i use CONTAINS(myfield, '"00001234*"')
that's fine and can locate the exactly record
then how can i solve this probem ?
"Daniel Crichton" <msnews@.worldofspack.co.uk> glsD:uyc9f22XFHA.1384@.TK2MSFTNGP09.phx.g bl...
>Q wrote on Sat, 21 May 2005 09:46:51 +0800:
>
> You'll need to split your clause up, eg.
> contains(myfield,'the') and contains(myfield,'"apple*"')
> Dan
>
|||Q wrote on Mon, 23 May 2005 16:34:24 +0800:

> one more question
> i encounter the case
> when i want to search "00001234" <-- this no.
> my SQL is like this CONTAINS(myfield, '"1234*"')
> however, it cannot locate the record
> If i use CONTAINS(myfield, '"00001234*"')
> that's fine and can locate the exactly record
> then how can i solve this probem ?
You can't do suffix searching in FTS. "1234*" means "look for any term that
starts with 1234". The second version works because you're asking it to find
all terms starting with 00001234, and the value you're looking for matches
that (as nothing following the term will match the wildcard).
To do a suffix search you'll need to use LIKE, eg:
myfield LIKE '%1234'
Dan