SQL - querying via a textbox which could take different values

Question

Developing a website and just trying to get back into the swing of (clever) SQL queries etc, my mind had totally gone tonight!

There is a website http://www.ufindus.com/ which has a textbox allowing you to enter either a place name or a postcode/zipcode. I am trying to do something similiar but I am rubbish at SQL - so how do you construct an SQL statement that could potentially look at 2 columns (i.e. place and postcode) because you can't query both fields for the same value e.g

place = 'YORK' AND postcode = 'YORK'

or

place = 'YO21 5EA' AND postcode = 'YO21 5EA'

so do you have to put some logic in to be intelligent enough to detect whether it looks like a place name or a postcode - that just seems too complicated to me!! Any help would be much appreciated.

Answer 1

You could use an "OR" to get the job done. For example,

place = 'YORK' or postcode = 'YORK'

You might also do better using the LIKE statement, as in

WHERE place LIKE 'YORK%' or postcode LIKE 'YORK%'

(this assumes both place and postcode are character-based columns)

Answer 2

why not use OR instead of AND?

place = @textboxvalue OR post = @textboxvalue

Answer 3

What's wrong with attempting to match on the place and postcode? If I put in 'York' and (somewhere) that happens to be a valid postcode, I should get that result. As for preventing the same thing being entered twice, well, you can handle that on the validation prior to doing the database call.

Ah. Guess I was a bit slow on the up-take. Yes... what the others suggested is right, 'OR' is what you were looking for. I misinterpreted.

Answer 4

Ok, first I'm assuming that you have a table with a mapping of postcodes to placenames.

Let's call this table 'postcode' with columns 'postcode' and 'postplace'. Both of these are of a char-type.

Then.. whatever you do, make sure the input from the user is not part of dynamic sql. Make sure it is a parameter. Otherwise, you are inviting SQL injection attacks that can really ruin your day. This is important.

Our user input is in @textboxstring.

Given this, you can get the postcode and postplace like this:

select @textboxstring = RTRIM(@textboxstring) + '%'; select postcode, postplace from postcode where postcode like @textboxstring or postplace like @textboxstring;

Note that I'm modifying @textboxstring to get wildcard match with like without having to use dynamic sql.

If the postcode was integer, you would need to convert the input to int before executing the sql. So with a @textboxint as well, you could do this:

select @textboxstring = RTRIM(@textboxstring) + '%'; select postcode, postplace from postcode where postcode = @textboxint or postplace like @textboxstring;

Oh, and you need to handle that your search can have multiple results. You probably only want the first row.