Asp and sql help with like and wildcard

Hi everyone

I have a database of postcodes. People go onto our website to book a place at our events. However if the postcode they entered is not in the database they will receive an email.

I have used the sql like and wildcard functions and they work well, the problem is, the postcode entered by the user is longer than the postcodes in my database.

For example in the database the postcode is only the first 3/4/5 characters (i.e. CV37 6) and a user will type in their full postcode (i.e. CV37 6KL)

Here is my sql statement:

strSQL = “SELECT * FROM postcodes_DB WHERE postcodes LIKE '” & search & "%’ "

So if I type in the full postcode in the field, I would like CV37 6 come up in the search results. How can I make this happen?

Hope that makes sense.

Any advice/help?


You are a star! thank you so much!

apologies, i replied with mysql syntax, i should have guessed you were using microsoft SQL Server

... WHERE '" & search & "' LIKE postcodes+'%'


Hi, Thanks for the reply.

I tried the code you have given above and got the following error:

Microsoft OLE DB Provider for SQL Server error ‘80040e14’
‘CONCAT’ is not a recognized built-in function name.

This is my code: -

strSQL = “SELECT * FROM postcodes_DB WHERE '” & search & “’ LIKE CONCAT(postcodes,‘%’)”

Am I missing something?

Thanks again

WHERE $search LIKE CONCAT(postcodes,‘%’)