[thelist] SQL wildcards for columns

Rob Smith rob.smith at THERMON.com
Wed Oct 13 12:06:12 CDT 2004


>...it would be very handy!

Indeed. Try these:

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

I hear the SQL CONTAINSTABLE is really good for this:


allproducts=split(Replace(request.form("searchtearms"), "'", "''")," ")
maxcounter=ubound(allproducts)
mySQL = "SELECT * From myTable INNER JOIN CONTAINSTABLE(myTable, *, '""*" &
allproducts(0) & "*"" "
for Counter=0 to maxcounter
   mySQL = mySQL & "OR ""*" & allproducts(counter) & "*"" "
next
mySQL = mySQL & "', " & Results & ") KEY_TBL ON myUniqueID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK desc"


Heads up, this table needs to be indexed for CONTAINSTABLE to work. It's
works rather neat once it's set up correctly.

Rob


More information about the thelist mailing list