Allowing the omission of 'AND'   

Hi all,
Probably a complete FAQ/newbie question, but i'm not finding it (or
searching wrongly).
Suppose my user wants to do a search for two words, they want to do an
AND-search and they don't want to type in AND. i.e. 'John Smith'
should be 'John AND Smith' for CONTAINSTABLE to return any results,
but the user will type in 'John Smith' and expect to see both 'John
Smith' and 'Smith, John' (so no exact string matching, only AND-
connected search).
Is there a standard way to tell CONTAINSTABLE that it should connect
all separate words with AND?
TIA,
Chris

date: Thu, 11 Oct 2007 01:35:13 -0700   author:   Zark3

Re: Allowing the omission of 'AND'   

Chris,

Well, providing there are no extra complexitities in the value they can 
type, you can handle adding the AND yourself.  One shorthand that may work 
is to do this prior to issuing the query prepare the string by:

set @string = REPLACE(REPLACE(REPLACE(Replace(ltrim(rtrim(@string)),
     ' ',' '),' ',' '),' ',' '), ' ', ' AND ')

RLF

"Zark3"  wrote in message 
news:1192091713.831150.254450@19g2000hsx.googlegroups.com...
> Hi all,
> Probably a complete FAQ/newbie question, but i'm not finding it (or
> searching wrongly).
> Suppose my user wants to do a search for two words, they want to do an
> AND-search and they don't want to type in AND. i.e. 'John Smith'
> should be 'John AND Smith' for CONTAINSTABLE to return any results,
> but the user will type in 'John Smith' and expect to see both 'John
> Smith' and 'Smith, John' (so no exact string matching, only AND-
> connected search).
> Is there a standard way to tell CONTAINSTABLE that it should connect
> all separate words with AND?
> TIA,
> Chris
>

date: Thu, 11 Oct 2007 14:31:23 -0400   author:   Russell Fields

Re: Allowing the omission of 'AND'   

Hello Zark3,

You need to do it in your client code really.

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


> Hi all,
> Probably a complete FAQ/newbie question, but i'm not finding it (or
> searching wrongly).
> Suppose my user wants to do a search for two words, they want to do an
> AND-search and they don't want to type in AND. i.e. 'John Smith'
> should be 'John AND Smith' for CONTAINSTABLE to return any results,
> but the user will type in 'John Smith' and expect to see both 'John
> Smith' and 'Smith, John' (so no exact string matching, only AND-
> connected search).
> Is there a standard way to tell CONTAINSTABLE that it should connect
> all separate words with AND?
> TIA,
> Chris

date: Mon, 15 Oct 2007 22:56:13 +0000 (UTC)   author:   Simon Sabin ail