Keying Into Smart Database Searches

Writing your own little search routine has never been easier. But consider the fact that your users would like to do partial text searches on data within your database and it gets worse. You can't retrieve records that match the text "ASP Pro" just by doing a search on the text "pro" and using an equal to sign. This tip explains how to use the LIKE SQL clause to do partial text searches.

Question:
When setting up a recordset, is it possible to get a record when the field contains more than the word that I'm looking for and where the words are in no particular order?

For example:

StrName = 'Direct'
RSShops = Server.CreateObject("ADODB.Recordset")
strSql = "SELECT * FROM Shops WHERE Name =
'" & StrName & "'

Can I get the recordset to contain any shop/company with 'direct' in it's name?

Answer:
Instead of using the Equal to (=) operator, use the LIKE operator in your SQL Statement.

To hunt for Shops with names that have the word 'direct' in them, your SQL statement would look like this:

SELECT * FROM Shops WHERE Name LIKE '%direct%'

You can thus modify your code accordingly.