Searching ASP.NET SQL DataSet for Postcode

Question

Hi

I am doing a project in ASP.NET that at one point searches a SQL database for a postcode using Datasets:

string postcode = "%" + searchTerm.Trim().Replace(' ', '%') + "%";
SearchDataSet.SearchCustomerTableDataTable custTable = custAdapter.GetDataCustPostcode(postcode);

The GetDataCustPostcode runs:

SELECT * FROM CustomerTable WHERE (CustomerPostcode LIKE @CustPostcode)

The expected results are returned when I try:

searchTerm = "BT14" searches for a postcode of %BT14%

or searchTerm = "BT14 7" searches for a postcode of %BT14%7%

custTable is empty when I try:

searchTerm = "BT14 7D" searches for a postcode of %BT14%7D%

If I try writing a SQL query directly, i.e. typing:

SELECT * FROM CustomerTable WHERE (CustomerPostcode LIKE '%BT14%7D%')

Then the expected results are returned.

Can anyone advise why this is? Is it something to do with the characters in the string?

Thanks

Clivest

Answer 1

Consider using the single-character wildcard, the underscore, for mid-string matching, and percents only on the ends of the string.

Answer 2

%7D is a special character in some circumstances... perhaps this one as well? Does the problem also happen for other combinations?