This tip demonstrates how to use special characters like _, %, [ to perform a wildcard search on string values stored in database (SQL Server in this instance)
Let us take the example of a table named “TIPS“, which contains a TITLEcolumn (that holds the title of all Tips ).
Now, If you want to search a tip with the title ‘Page_Load‘ then your query would be something like the following –
[TITLE] LIKE‘%Page_Load%’ ;
The above query will match all rows that have the value:
Page_Load in ASP.NET
Web page Page_Load() call
The query returns the result we were expecting but is it absolutely correct ? If you take another look at the output window you would find that the query also returned rows whose values are something like the following :
Page Loading in WebSite
How to fix PagerLoading BUG.
But this is not exactly what we were searching for! The reason that these rows were returned as results, is the character “_” (underscore) which is a wildcard character to match any random single character. Other special characters, that can be used with SQL Server are “%” and “[“. When these characters are used for searching for strings, the Server treats them as wildcard characters and literal.
So is it not possible to search database for a string that contains these special characters ?
Yes, the solution is to escape the character(s) by putting the it between “[ ]”