Hi All!



How do I write a search form that can take multiple words from a text field,

and feed that to a select statement, so it does a boolean search.



Currently in my ASP is use the split and join functions to rewrite the

string so it is ready to be inserted into the IN statement in my SELECT:



Keywords = Split(keywords, " ", -1, 1)

Keywords = Join(Keywords, "', '")



-----

		(SELECT keywordId FROM tblKeywords WHERE 

				KeywordName IN ('tax', 'business'))



I would like to rewrite the code so that I can use wildcards with it,

someting like:



				KeywordName IN ('tax%', '%business')



So that the search form will also get results for 'taxes' and 'e-business',

however as far as I can tell, you are not allowed to put wildcards in a

delimited IN statement, because they will be treated literally.



Any help would be much appreciated.

    And I always thought IN was compatable with wildcards.



    Well, I had thought that maybe an easy solution would be to use SOUNDEX.

Using SOUNDEX as follows:



SELECT city FROM site WHERE SOUNDEX(city) IN (SOUNDEX('New York'),

SOUNDEX('Virginia Beach'))



    Yielded something like:



New York

Virginia Beach

N Richland Hills

New York

N.Richland



    So that doesn't sound good.  You might end up making a clever REPLACE

using LIKE with ORs.



- Chuck



----- Original Message -----

From: "De Beer, Joris (AU - Melbourne)" <joris_de_beer@d...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, April 19, 2001 8:41 PM

Subject: [asp_databases] Using a wildcard with IN





> Hi All!

>

> How do I write a search form that can take multiple words from a text

field,

> and feed that to a select statement, so it does a boolean search.

>

> Currently in my ASP is use the split and join functions to rewrite the

> string so it is ready to be inserted into the IN statement in my SELECT:

>

> Keywords = Split(keywords, " ", -1, 1)

> Keywords = Join(Keywords, "', '")

>

> -----

> (SELECT keywordId FROM tblKeywords WHERE

> KeywordName IN ('tax', 'business'))

>

> I would like to rewrite the code so that I can use wildcards with it,

> someting like:

>

> KeywordName IN ('tax%', '%business')

>

> So that the search form will also get results for 'taxes' and

'e-business',

> however as far as I can tell, you are not allowed to put wildcards in a

> delimited IN statement, because they will be treated literally.

>

> Any help would be much appreciated.

Yeah, I'll have to do something like:



Keywords = Replace(Keywords, " ", "%' OR keywordName LIKE '%")



SELECT keywordId FROM tblKeywords WHERE 

KeywordName LIKE '%trowbridge%' OR keywordName LIKE '%business%'



Thanks for the SOUNDEX tip Charles, I was going to try that, but now I won't

bother.



There really must be a better way to create a Search Form though. I want a

simple way to do Boolean, Allwords, Anyword, and Phrase searching.





-----Original Message-----

From: Charles Feduke [mailto:webmaster@r...]

Sent: Friday, April 20, 2001 1:12 PM

To: ASP Databases

Subject: [asp_databases] Re: Using a wildcard with IN





    And I always thought IN was compatable with wildcards.



    Well, I had thought that maybe an easy solution would be to use SOUNDEX.

Using SOUNDEX as follows:



SELECT city FROM site WHERE SOUNDEX(city) IN (SOUNDEX('New York'),

SOUNDEX('Virginia Beach'))



    Yielded something like:



New York

Virginia Beach

N Richland Hills

New York

N.Richland



    So that doesn't sound good.  You might end up making a clever REPLACE

using LIKE with ORs.



- Chuck

> There really must be a better way to create a Search Form though. I want a

> simple way to do Boolean, Allwords, Anyword, and Phrase searching.



	Have you looked at full text indexing?  I've never touched it, but I know

its really useful in content searches like this.



? Chuck





> -----Original Message-----

> From: De Beer, Joris (AU - Melbourne)

> [mailto:joris_de_beer@d...]

> Sent: Friday, April 20, 2001 5:19 AM

> To: ASP Databases

> Subject: [asp_databases] Re: Using a wildcard with IN

>

>

> Yeah, I'll have to do something like:

>

> Keywords = Replace(Keywords, " ", "%' OR keywordName LIKE '%")

>

> SELECT keywordId FROM tblKeywords WHERE

> KeywordName LIKE '%trowbridge%' OR keywordName LIKE '%business%'

>

> Thanks for the SOUNDEX tip Charles, I was going to try that, but

> now I won't

> bother.

>

> There really must be a better way to create a Search Form though. I want a

> simple way to do Boolean, Allwords, Anyword, and Phrase searching.

>

>

> -----Original Message-----

> From: Charles Feduke [mailto:webmaster@r...]

> Sent: Friday, April 20, 2001 1:12 PM

> To: ASP Databases

> Subject: [asp_databases] Re: Using a wildcard with IN

>

>

>     And I always thought IN was compatable with wildcards.

>

>     Well, I had thought that maybe an easy solution would be to

> use SOUNDEX.

> Using SOUNDEX as follows:

>

> SELECT city FROM site WHERE SOUNDEX(city) IN (SOUNDEX('New York'),

> SOUNDEX('Virginia Beach'))

>

>     Yielded something like:

>

> New York

> Virginia Beach

> N Richland Hills

> New York

> N.Richland

>

>     So that doesn't sound good.  You might end up making a clever REPLACE

> using LIKE with ORs.

>

> - Chuck

>

>