An ASP include that exposes a class to generate an SQL statements

<script language="JSCRIPT" RUNAT=Server>
//------------------------------------------------------------------------------
//This is a generic Search keyword to SQL statement include.
//the library takes search keywords and produces a equivalent SQL statement.
//the search syntax accounts for advance symbols
// + : include filter (the search result must include the keyword.
// - : exclude filter, search result must not contain keyword.
// & : logical AND the search must contain keyword1 AND keyword2
// | : logical OR the search must contain keyword1 OR keyword2
// ! : logical NOT the search must contain keyword1 BUT NOT keyword2
//------------------------------------------------------------------------------
//Written By: Salim Naim 26/2/2000
//Note: I'm more than happy to fix any Bugs or answer any questions regarding the
//sample,i can be reached at snaim@zfp.com or salimn@yahoo.com
//------------------------------------------------------------------------------
//Warning: This sample is assuming some knowlegde of the Structured Query Language(SQL)
//------------------------------------------------------------------------------

//------------------------------------------------------------------------------------
//function: _addword
//Description: the keyword object.
//------------------------------------------------------------------------------------
function _word()
{
this.phrase="";
this.type=""; 
}

//------------------------------------------------------------------------------------
//function: _addword
//Description: the helper function that adds a keyword object to the KeywordList
//------------------------------------------------------------------------------------
function _addword(phrase,type)
{
var obj = new _word()
obj.phrase = phrase;
obj.type = type;
return(obj);
}

//------------------------------------------------------------------------------------
//function: KeywordList
//Description: keyword link list.
//------------------------------------------------------------------------------------
function KeywordList()
{
this.count=0;
//the word is an object 
this.word = new _word();
this.add = AddKeyword;
return this;
}


//------------------------------------------------------------------------------------
//function: AddKeyword
//Description: Add unique keywords into the search keyword list.
//------------------------------------------------------------------------------------

function AddKeyword(word,type)
{
if(word.length > 0 ){
for(var i=0;i<this.count;i++)
if(this.word[i].phrase == word)
return; 
this.word[this.count++] = _addword(word,type); 
}
}

//------------------------------------------------------------------------------------
//function: normalizeWord
//Description: this function normalizes words inorder to increase hit results.
//------------------------------------------------------------------------------------
function normalizeWord(keyword)
{ 
var esc = keyword.toLowerCase(); 
var kw = "";

for(var i=0;i < esc.length;i++){
var ch = esc.charAt(i);
if(ch == '%')
i += 2;
else
kw += ch;
}

var len = kw.length; 
if (kw.charAt(len-1) == "s" && kw.charAt(len-2) != "s"){
kw = kw.substring(0,len-1);
len--;
}
//acounnt for the "
if (kw.charAt(len-1) == "\""){
kw = kw.substring(0,len-1);
len--;
} 
if (kw.charAt(0) == "\""){
kw = kw.substring(1,len);
len--;
} 

if(kw == "in" && len == 2){
kw = "";
len -=2;
}
if(kw.substring(len-2,len) == "ly"){
kw = kw.substring(0,len-2);
len -= 2;
}
if(kw.substring(len-2,len) == "ed"){
kw = kw.substring(0,len-1);
len --;
}
if(kw.substring(len-2,len) == "er"){
kw = kw.substring(0,len-1);
len --;
}
if(kw.substring(len-2,len) == "ie"){
kw = kw.substring(0,len-2) + y;
len --;
}
if(kw.substring(len-3,len) == "ing" && len > 5){
kw = kw.substring(0,len-3);
len -= 3;
if(isVowel(kw.charAt(len-2)) && !isVowel(kw.charAt(len-3))){
kw += "e";
len++;
}
}
if(kw.charAt(len-1) == "e")
if(!isVowel(kw.charAt(len-3))){
kw = kw.substring(0,len-1);
len--;
}
if(len > 1 && (kw.charAt(len-1) == kw.charAt(len-2))){
kw = kw.substring(0,len-1);
len--;
} 
return(kw); 
}

//------------------------------------------------------------------------------------
//function: isStopword
//Description: this function excluded words that should not be classified as keywords.
//------------------------------------------------------------------------------------
function isStopword(word)
{
var wd = word.toLowerCase();
if(wd == "a" || wd == "an" || wd == "and" ||
wd == "or" || wd == "the" || wd == "is" || wd == "this" || wd == "all")
return(true);
else
return(false);

}

//------------------------------------------------------------------------------------
//function: parseKeywords
//Description: this function adds unique filenames to the list of database fields that
// we want to search.
//------------------------------------------------------------------------------------
//Note: this is function was adopted from the BOOK using javascript, modifications have
// been added inorder to add keyword search symbols
//------------------------------------------------------------------------------------
function parseKeywords(str)
{
var list = new KeywordList();
var inword = false;
var word = "";
var len = str.length; 
var firstcharacter = true;
var Wordtype = ""; 

if(len <=0 )
return

for(var i=0;i<len;i++){
var ch = str.charAt(i);
//check the first character in the keyword
if(firstcharacter){ 
if(isSymbol(ch))
Wordtype = getSymbolType(ch); 
firstcharacter = false;
} 

if(isWhitespace(ch) || isDelimiter(ch)){ 
if(inword){ 
if(!isStopword(word))
list.add(normalizeWord(word),Wordtype);

word = "";
inword = false;
firstcharacter = true 
} 
}
else{ 
word += ch;
inword = true;
}

if( i+1 == len && inword)
if(!isStopword(word)){ 
list.add(normalizeWord(word),Wordtype);
}
} 
return list
}

//------------------------------------------------------------------------------------
//function: AddFieldName
//Description: this function adds unique filenames to the list of database fields that
// we want to search.
//------------------------------------------------------------------------------------
function AddFieldName(fieldname)
{
if(fieldname.length > 0 ){
for(var i=0;i<this.count;i++)
if(this.fields[i] == fieldname)
return; 
this.fields[this.count++] = fieldname; 
}
}

//------------------------------------------------------------------------------------
//function: MakeSQLString
//Description: this function makes the SQL statement based on the list of
// keyword being parsed by the string parser.
//------------------------------------------------------------------------------------
//Note: this is an internal function use by the SearchSQL class
//------------------------------------------------------------------------------------
function MakeSQLString(vbstr)

{ var strSQL = ""; 
var fieldname = ""; 
var szlogic = "";
var szNot;
var fDonefirst = false;
var ifieldstosearch=0;

//okay the string getting passed from
//VbScript is a variant, create a String object
//with this parameter because we expect a String.
var str = new String(vbstr);

if (!(str.length > 0))
Response.Write("must pass a search string");

//Now Parse the keywords 
var list = parseKeywords(str);

//start of statement
if(this.columns.length > 0)
strSQL += "SELECT " + this.columns + " FROM " + this.tablename;
else
strSQL += "SELECT * FROM " + this.tablename;

//account for everything accept filter because filters
//should be the last thing that controls the SQL statement

if (list.count > 0)
strSQL += " WHERE ("

//now loop through the keywords in the search string. 
for(var i=0;i<this.count;i++){
//inner loop through the fieldnames that we should
//search in the database.
for(var j=0;j<list.count;j++){
ifieldstosearch++;
//exclude the filter for now.
if(!(list.word[j].type == "filerminus" || list.word[j].type == "fileradd" )){
//get the phrase and setup the SQL logic
//symbols.
szPhrase = list.word[j].phrase 
szNot = "" 

//now the filter can be annoying if the number of fields
//getting searched are more than 3,so change logic to OR
//so that we can have a better chance of a hit.
if(ifieldstosearch>2) 
szlogic = " OR "
else
szlogic = " AND "

//now check the frist character of the 
//keyword is it an OR || NOT
if(szPhrase.length > 0){
if(isSymbol(szPhrase.charAt(0))){ 
if(szPhrase.charAt(0) == '|')
szlogic = " OR "; //OR logic 
else if(szPhrase.charAt(0) == '!'){ 
szNot = " NOT "; //NOT LOGIC
} 
//trim that character out. 
szPhrase = szPhrase.substring(1,szPhrase.length-1); 
} 
}
//now the first time that we add the clause to the SQL
//we have to make sure that we don't add the LOGIC
if(!fDonefirst){
fieldname += " " + this.fields[i] + szNot + " LIKE \'%" + szPhrase + "%\'";
fDonefirst =true;
}
else 
fieldname += szlogic + this.fields[i] + szNot + " LIKE \'%" + szPhrase + "%'";

}
} 
}
//now done with the main search statement
//lets start with the filters. 
fieldname += ") "


//filters.
//filter are usually :
//+{keyord} : to make sure that the result contains the keyword
//-{keyowrd} : to make sure that the result excludes the keyword
for(var i=0;i<this.count;i++){
for(var j=0;j<list.count;j++){
//only work with the filters.
if(list.word[j].type == "filerminus" || list.word[j].type == "fileradd"){
szPhrase = list.word[j].phrase 
//trim the symbol
szlogic = ""
if(szPhrase.length > 0){
//Now filters are easy usually they are a Logic AND
//incase of - then they should be a NOT AND
if(isSymbol(szPhrase.charAt(0))){
if(szPhrase.charAt(0) == '-')
szlogic = " NOT " 
szPhrase = szPhrase.substring(1,szPhrase.length-1); 
} 
}
//add to critera
fieldname += " AND " + this.fields[i] + szlogic + " LIKE \'%" + szPhrase + "%'"; 
}
} 
} 

//Now build the SQL statement
//and return it. 
strSQL += fieldname; 
return(strSQL);
}

//------------------------------------------------------------------------------------
// function: SearchSQL
//Description: this is the master class that we instantiate for our search include
//------------------------------------------------------------------------------------
//Note: this class is created via the helper function CreateSearchClass
//------------------------------------------------------------------------------------
function SearchSQL()
{
this.tablename = "";
this.innerjoin = false;
this.fields = new Object();
this.AddField = AddFieldName;
this.count=0;
this.execute = MakeSQLString;
this.columns = "";
}
//------------------------------------------------------------------------------------
// function: CreateSearchClass
//Description: this function creates the SearcgSQL class.
//---------------------------------------------------------------------------------
function CreateSearchClass(){return new SearchSQL()}

//helper functions
function getTime()
{
var s="";
var c=":";
d = new Date();
s += d.getHours() + c;
s += d.getMinutes() + c;
s += d.getSeconds() + c;
s += d.getMilliseconds(); 
return(s)
}

function isWhitespace(ch){
if(ch == ' ' || ch == '\n' || ch == '\r' || ch == '\t' || ch == '\f' ||
ch == '\v' || ch == '\b')
return(true)
else
return(false); 
}

function isSymbol(ch)
{
if(ch == '-' || ch == '+' || ch == '&' || ch == '|' || ch == '!')
return(true);
else
return(false);
}

function getSymbolType(ch)
{
var szType = ""; 
if(ch == '-')
szType = "filerminus";
else if(ch == '+') 
szType = "fileradd";
else if(ch == '&') 
szType = "logicand"; 
else if(ch == '|') 
szType = "logicor"; 
else if(ch == '!') 
szType = "logicnot"; 
else 
szType = "normal";

return(szType);
}

function isDelimiter(ch)
{
if(ch == ',' || ch == '?' || ch == '.' || ch == '\\' || ch == '/')
return(true);
else
return(false);
}

function isVowel(ch)
{
if(ch == 'a' || ch == 'e' || ch == 'i' || ch == 'o' || ch == 'u' || ch == 'y')
return(true);
else
return(false);
}
</script>