help with a like query

[sandl4597]

my code is similar to what you see below. I am not using a stored procedure and i'm not going to but the problem is this. this query below when executed only checks the first part of the "myfield". as an example if i were to type in the word dog for "myinput", i would want the query to return all records that contain the word dog somewhere in "myfield". what do i have to change to get this query to do that. thanks.


Dim strSearch
Dim strSQL

strSearch = Request.Form("myinput&quot

strSQL = "SELECT * FROM mytable WHERE myfield LIKE '" & strSearch & "%'"

 

[JamesLean]

strSQL = "SELECT * FROM mytable WHERE myfield LIKE '%" & strSearch & "%'"

--James

 

[SQLSister]

Try:
strSQL = "SELECT * FROM mytable WHERE myfield LIKE '" + strSearch + "%'"

A couple of points to consider. First generally it is bad to use Select * unless you are absolutely sure you will need every one of the fields. This increases the amount of data sent across the network consisderably and can cause a slow system.

Second, Like is not very efficient for searching but at least you don't have the % in front of the string which makes it impossible to use an index at all. Of course if you truly want all records that contain the word Dog, you would need the % in fron of the search term or you wouldn;t find the record that has hairy dog in it. If this is truly your requirement, I suggest you look at full-text search which is much more efficient than using the like statement with an % in the front.

Third, Coding the SQL in the user interface is also a bad practice for many reasons. First it means that cached execution plans can't be used, second it often results in more network traffic if the statements are long. It also makes it more difficult to maintain the database. It is more difficult to code and debug complex queries. It also makes the system less secure as the dba must grant rights on the tables instead of just to the stored procedure. It encourages sloppy coding practices (like select *) because the dba doesn't ever see the code. And it discourages the usse reuse of code.