Why not have your Java client code split the postcode e.g. "PE10 1AF"
into two parts, "PE10" and "1AF", then look for an exact match to the
first part?

British postcodes have a regular structure, so this will always work,
and in addition, the prefix part "PE10" always refers to a meaningful
geographic region, so it makes sense from this viewpoint too.

David Harper
Cambridge, England

You definitely want to use LIKE for this problem. However, since you are
searching on multiple patterns, you'll have to OR a few LIKEs together.

LIKE uses two wild card characters, % and _. % represents any number of
unknown characters (including 0), and _ represents exactly one unknown
character. Therefore,
WHERE POSTALCODE LIKE 'P%'
says that you want any row where the postal code starts with a P and you
don't care what comes after it. Even a postal code that was *just* P with
nothing after it would satisfy this search.

WHERE POSTALCODE LIKE 'ABCDE_G' would find rows that had ABCDE in the first
five characters of the postal code, G in the seventh position, and any
character at all in the 6th position.

So, if you combine these wild card characters, you should be able to write
LIKE searches that do what you want.

You'll probably want something like:

select * from mytable
where postalcode like '%PE10%'
or postalcode like '%PE15%'

You can improve on that if you know exactly where the partial postal code
is. For example if PE10, PE15 and PE1 always start at the fourth position,
you could do this:

select * from mytable
where postalcode like '___PE10%'
or postalcode like '___PE15%'

(The example has 3 underscores at the start of each like search.)

The only problem you'll have is with the PE1 codes. Since PE1 is a subset of
PE10 and PE15, PE12 will match with PE1% but won't be a row that you want to
see.

Rhino

Rhino