MySQL Select help needed

java-programmer, MySQL Select help needed Hi All,

Trying to work out the correct sql statement

If I have a table that contains the column partpostcode and has these
entries,
PE10
PE15
PE1

What I want to do is select those rows where the partpostcode exists in
a given parameter,...

i.e something liek.

Select * where partpostcode IS CONTAINED IN "PE10 1AF"

sadly there are no IS CONTAINED IN keywords, but i'm not sure how to do
it using LIKE or IN...

Any comments ?

Thanks,

java-programmer >> MySQL Select help needed Dave Brown wrote:
> If I have a table that contains the column partpostcode and has these entries,
> PE10
> PE15
> PE1
>
> What I want to do is select those rows where the partpostcode exists in a given parameter,...
>
> i.e something liek.
>
> Select * where partpostcode IS CONTAINED IN "PE10 1AF"

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

java-programmer >> MySQL Select help needed Hi David,

My concern there is when I get a Postcode that may not have a space
delimiter.

Dave.
David Harper wrote:
> Dave Brown wrote:
>
>> If I have a table that contains the column partpostcode and has these
>> entries,
>> PE10
>> PE15
>> PE1
>>
>> What I want to do is select those rows where the partpostcode exists
>> in a given parameter,...
>>
>> i.e something liek.
>>
>> Select * where partpostcode IS CONTAINED IN "PE10 1AF"
>
>
> 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
>

java-programmer >> MySQL Select help needed Dave Brown wrote:
> My concern there is when I get a Postcode that may not have a space
> delimiter.

That's fair enough, but if someone types "PE10 1AF" without space, as
"PE101AF", you can still separate the two parts, because the second part
is *always* a single digit followed by two letters.

Therefore, if there's no space, the algorithm is as follows ...

String postcode = "PE101AF";

int pclen = postcode.length();

String prefix = postcode.substring(0, pclen - 3);

String suffix = postcode.substring(pclen - 3);

Of course, if the user enters a partial postcode such as PE10, this is
ambiguous: is is "PE10" (Peterborough district 10), or "PE1 0"
(Peterborough district 1, sub-district 0)?

In these cases, you can probably safely assume that the user is giving
the prefix part of their postcode i.e. the user meant "PE10", and not
"PE1 0".

Or you could pop up an error message and insist on a full postcode :-)

David Harper
Cambridge

java-programmer >> MySQL Select help needed Thanks David.

BTW, I just noticed you are from Cambridge, Have you any interest in
the East Anglian Java User Group ? I contacted them last week to see
what kind of interest there was, currently theres only a handfull of
people on the list of interested parties. Theres a link to them from
SUN's JUG pages if you are interested.

Rgds,

Dave

David Harper wrote:
> Dave Brown wrote:
>
>> My concern there is when I get a Postcode that may not have a space
>> delimiter.
>
>
> That's fair enough, but if someone types "PE10 1AF" without space, as
> "PE101AF", you can still separate the two parts, because the second part
> is *always* a single digit followed by two letters.
>
> Therefore, if there's no space, the algorithm is as follows ...
>
> String postcode = "PE101AF";
>
> int pclen = postcode.length();
>
> String prefix = postcode.substring(0, pclen - 3);
>
> String suffix = postcode.substring(pclen - 3);
>
> Of course, if the user enters a partial postcode such as PE10, this is
> ambiguous: is is "PE10" (Peterborough district 10), or "PE1 0"
> (Peterborough district 1, sub-district 0)?
>
> In these cases, you can probably safely assume that the user is giving
> the prefix part of their postcode i.e. the user meant "PE10", and not
> "PE1 0".
>
> Or you could pop up an error message and insist on a full postcode :-)
>
> David Harper
> Cambridge
>

java-programmer >> MySQL Select help needed
"Dave Brown" <email***@***.com> wrote in message
news:d43g7k$8o3$email***@***.com...
> Hi All,
>
> Trying to work out the correct sql statement
>
> If I have a table that contains the column partpostcode and has these
> entries,
> PE10
> PE15
> PE1
>
> What I want to do is select those rows where the partpostcode exists in
> a given parameter,...
>
> i.e something liek.
>
> Select * where partpostcode IS CONTAINED IN "PE10 1AF"
>
> sadly there are no IS CONTAINED IN keywords, but i'm not sure how to do
> it using LIKE or IN...
>
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