Re: MySQL Select help needed


"Dave Brown" <dave@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:d43g7k$8o3$1@xxxxxxxxxxxxxxxxxxxxxx
> 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


.