0

I am trying to find records where the post code starts with a certain set of characters from some 10,000 records, 1300 or so of which meet the select below. My code is

Select * from table where mid(postcode,1,2 ) IN ("CV DE LE MK NN")

This returns a zero result A typical postcode would be something like CV1 9AD

If I write

Select * from table where mid(postcode,1,2 ) = "CV"

I get the full list of records for post codes beginning with "CV" I would have thought that the IN statement would work. Can anyone tell me why it does not?

3

The IN operator expects a list of items, not a single string. You need to structure your query as follows:

Select * from table where mid(postcode,1,2) IN ('CV','DE','LE','MK','NN');
1

You could also use the string comparison operators:

SELECT * FROM My_Table WHERE postcode LIKE 'CV%';

You can also make use of the powerful REGEXP operators:

SELECT * FROM My_Table WHERE postcode REGEXP '[B-D][U-W]'; (will get the first letter from B to D and second letter from U to W, and so will pick up your desired postcodes - this was just a trivial example for illustrative purposes.

Regular expressions can seem both forbidding and a pain to learn at first (partially true!), but once mastered, quickly become indispensable.

Finally, take some time to peruse the docco - there are lots of (sometimes quirky) functions which can come in handy.

  • +1 for the LIKE. I have yet to find a need to use regexes. – ypercubeᵀᴹ Jan 31 '15 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.