How do you create a dynamic SQL statement from multiple words submitted by 

a form when you don't know how many words there will be? ie: This is a 

search page that splits up the number of words into an array. I then need 

to take how ever many words there are and construct a SQL statement with 

multiple "LIKE" clauses.

if you make a search for exactly the same words

you can you IN.



SELECT * FROM <TABLE> WHERE <FIELD> IN (<VALUE1>,<VALUE2>,<VALUE3>,<VALUE4>)



if you need to use LIKE I think you `ll need to create SQL dynamically



While ....



	sSQL = sSQL & " field LIKE '" & value & " OR "



Wend



'	Take off last OR off

sSQL = Left(sSQL,Len(sSQL) - Len("OR "))





Hope this is what you after.

Oleg





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

From: Rick Lull [mailto:ricklull@h...]

Sent: March 13, 2002 4:57 PM

To: ASP Web HowTo

Subject: [asp_web_howto] Dynamic SQL Statment





How do you create a dynamic SQL statement from multiple words submitted by

a form when you don't know how many words there will be? ie: This is a

search page that splits up the number of words into an array. I then need

to take how ever many words there are and construct a SQL statement with

multiple "LIKE" clauses.




$subst('Email.Unsub').

Rick i presume u already know the table against which

u r going to search -



Here's the pseudo code for your problem -



1. let's take orders as the table to be searched for 

2. u accept the search parameter and form an array out

of it. U have to calculate the no. of items and

accordingly built the no. of LIKE(s) as



   if no. of words in array are 3 u do 



   dim likestmt

   dim arrcnt    'which is 3 for us in this case

   

   likestmt=""

   temp= "itemname like="

   if arrcnt >0 then 

   for(i=1;i<arrcnt;i++)

          likestmt = likestmt + temp+arrcnt[i]+"or "

   next

   likestmt = temp+arrcnt[i]

   'your like stament is ready

   'use response.write just to verify



   'now we get to actual sql query

   sql = "select * from orders where " + likestmt



   'again use response.write to print out 

 

   'execute ur qry and do further processing according

to ur requirements....



that's all my friend... if u need any other help just

drop a mail...



cheers,

naresh







--- Rick Lull <ricklull@h...> wrote:

> How do you create a dynamic SQL statement from

> multiple words submitted by 

> a form when you don't know how many words there will

> be? ie: This is a 

> search page that splits up the number of words into

> an array. I then need 

> to take how ever many words there are and construct

> a SQL statement with 

> multiple "LIKE" clauses.





$subst('Email.Unsub').





__________________________________________________

Do You Yahoo!?

Yahoo! Sports - live college hoops coverage

http://sports.yahoo.com/