Construct this SQL statement using VBScript 3

[puitar]

Greets,

I want to construct a SQL statement that queries table called FAQ. eg..

tSQLStr = "select * from FAQ where keywords like '" & txtSearchString & "'"

but...

txtSearchString might contain a few words like...

txtSearchString = "help bugger user interface"

I want the sql to look like...

select * from FAQ where keywords like "help" or keywords like "bugger" or keywords like "user" etc...

What's an easy way to construct the sql statement like this using ASP VBScript?

Any help appreciated!

 

[snowboardr]

Try:

"SELECT * FROM FAQ WHERE (keywords Like '" & "help" & "%') or" & "(keywords Like '" & "bugger" & "%') www.vzio.com
ASP WEB DEVELOPMENT

 

[Tarwn]

If you mean building it dynamically, here is an option:
Pretend your previous form has 5 textboxes named keyword1, keyword2, keyword3, etc

Dim sqlStmt, i
sqlStmt = "SELECT * FROM FAQ WHERE "

For i = 1 to 5
   If Request.Form(&quot;keyword&quot;&i) <> &quot;&quot; Then
      If i = 1 Then 
         sqlStmt = sqlStmt & &quot; keywords LIKE '&quot; & Request.Form(&quot;keyword&quot;&i) & &quot;' &quot;
      Else
         sqlStmt = sqlStmt & &quot;OR keywords LIKE '&quot; & Request.Form(&quot;keyword&quot;&i) & &quot;' &quot;
      End If
   End If
Next

Hope that helps
-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for "Forget Asking Questions, I am to busy"

 

[codestorm]

my take on it ..

dim txtSearchString, blnAllWords
txtSearchString=Request.Form("SearchString&quot
if len(Request.Form("AllWords&quot)>0 then
blnAllWords = true
else
blnAllWords = false
end if
if len(txtSearchString)>0 then
txtSearchString = split(txtSearchString, " &quot
dim tSQLstr, lngCount
tSQLstr = "select FAQID, FAQText from FAQ where "
for lngCount = lbound(txtSearchString) to ubound(txtSearchString)
if len(txtSearchString(lngCount))>0 then
tSQLstr = tSQLstr & "(keywords like '%" & trim(txtSearchString(lngCount)) & "%') "
if lng_Count < ubound(txtSearchString) then
if blnAllWords then
tSQLstr = tSQLstr & &quot;and &quot;
else
tSQLstr = tSQLstr & &quot;or &quot;
end if
end if
end if
next
end if codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[Tarwn]

Nice, I didn't consider writing it for a single text box, I like the addition of the flag for all words too. Guess I wasn't awake when I read the first post
Nice piece of code codestorm. The only improvements I would consider would be replacing the blnAllWords with a txt string instead. If you give it tha value of &quot;AND &quot; or &quot;OR &quot; that will cut down on processing just a little (one less if stmt) and using 0 instead of lBound() will cut it down a little more, as all arrays start with lbound = 0 when created by the scripting engine. If they have a leading space in their search string, the array will just end up with a null value in the first element, which your checking for already. The other would be the trim statement, which is unneccasary since we were splitting on space.
Here are my improvements, credit goes to codestorm for waking me up

Dim txtSearchString, txtConditional
Dim SQLstring
Dim searchArray

SQLstring = "SELECT * FROM FAQ "		'up front so that a blank search will return all

If Request.Form(&quot;txtKeywordSearch&quot;) <> &quot;&quot; Then
	Dim i
	searchArray = split(Request.Form(&quot;txtKeywordSearch&quot;),&quot; &quot;)

	If Request.Form(&quot;chkAllWords&quot;) <> &quot;&quot; Then
		txtConditional = &quot;AND &quot;
	Else
		txtConditional = &quot;OR &quot;
	End If
	For i = 0 to UBound(searchArray)
		If searchArray(i) <> &quot;&quot; Then
			SQLstring = SQLstring & &quot;keywords LIKE '%&quot; & Replace(searchArray,&quot;'&quot;,&quot;''&quot;) &&quot;%' &quot;
			If i < UBound Then SQLstring = SQLstring & txtConditional
		End If
	Next
	If Request.Form(&quot;chkOrderBy&quot;) <> &quot;&quot; Then	'chkOrderBy passes value for order, should handle multiple
		SQLString = SQLString & &quot; ORDER BY &quot; & Request.Form(&quot;chkOrderBy&quot;)
	End If
End If

Lets see who can cut this down to a single line
-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for &quot;Forget Asking Questions, I am to busy&quot;

 

[Tarwn]

Ooh, just thought of another improvement, what about wildcards? Maybe allow them to enter a star inside their string to act as a wildcard, just add a replace statement before the array split to replace * with %.
-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for &quot;Forget Asking Questions, I am to busy&quot;

 

[puitar]

Thanks!!!!!!!!!

Here's what I've come up with....


KeyWordArray=split(tKeywords,&quot; &quot
temp = &quot;keywords like '%&quot; & KeyWordArray(0) & &quot;%'&quot;

for i = 1 to ubound(KeyWordArray)
temp = temp & &quot; or keywords like '%&quot; & KeyWordArray(i)& &quot;%'&quot;
next

tWhereClause = tWhereClause & temp & &quot&quot;

tSQLStr = tSQLStr & tWhereClause

thanks again.