Be careful not to fall into the trap of adding the wildcard to the sql. It needs to be added to the parameter…
SELECT * FROM tblCustomers WHERE LastName LIKE '%mit%'
will return rows where the LastName field contains “mit”, at the beginning, end or anywhere with in the last name as there are 2 wildcards.
If you want to be able to specify the search criteria at run time, i.e. not just “mit”, replace the hard coded search with a parameter like @Search…
SELECT * FROM tblCustomers WHERE LastName LIKE '%@Search%'
If you are using something like a Sqlcommand in asp.net you will supply the parameter to it through perhaps a TextBox control so you would think you should do this…
Cmd.Parameters.Add("@Search", SqlDbType.NvarChar).Value = TextBoxSearch.Text
BUT THIS IS WRONG AND WON’T WORK!!!
The sql should not contain the wildcards. It should be :
SELECT * FROM tblCustomers WHERE LastName LIKE @Search
and the parameter should have the the wildcards:
Cmd.Parameters.Add(“@Search”, SqlDbType.NvarChar).Value = “%” + TextBoxSearch.Text + “%”