SELECT *
FROM Locations L, Brick B
WHERE (LEFT(L.'Post Code', LOCATE(' ', L.'Post Code') = LEFT(B.Brick,
LOCATE(' ', L.'Post Code')))
The functions if locate gives you the position of the character in the
string, if this is not what you whant you can search the mysql string
functions documentation in:
http://www.mysql.com/documentation/mysql/bychapter/manual_Functions.html#Str
ing_functions
----- Original Message -----
From: "Shaun" <shaun@xxxxxxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Sent: Tuesday, February 17, 2004 10:19 AM
Subject: Re: Brick Codes
> Thank you for your replies,
>
> The brick and postcode will never match so I need to compare the Postcode
in
> Locations to the Postcode in Brick. Something like:
>
> SELECT B.Brick
> FROM Locations L, Brick B
> WHERE (LEFT(L.Postcode,3) = LEFT(B.Postcode,3))
>
> The problem is that some of the postcodes stored in Brick have three
digits
> and some have four, is there a way of capturing everything before the
first
> space in L.Postcode and compare that to B.Postcode?
>
> Thanks for your help
>
> "Ignatius Reilly" <ignatius.reilly@xxxxxxx> wrote in message
> news:06b901c3f53e$9c371960$0100a8c0@xxxxxxxxx
> > Hmmm...
> >
> > Better try:
> > WHERE (LEFT( Brick,3) = LEFT(`Post Code`,3))
> > ^^ no quotes ^^ back ticks (because of
the
> > space in the column name)
> >
> > HTH
> > Ignatius
> > _________________________
> > ----- Original Message -----
> > From: "Ricardo Lopes" <lopes@xxxxxxxxxxxxxxx>
> > To: "Shaun" <shaun@xxxxxxxxxxxxxx>
> > Cc: "PHP DB" <php-db@xxxxxxxxxxxxx>
> > Sent: Tuesday, February 17, 2004 10:23
> > Subject: Re: Brick Codes
> >
> >
> > > That may depend of what database server you are using, for mysql you
can
> > > use:
> > >
> > > ........ WHERE (LEFT('Brick',3) = LEFT(Post Code,3))
> > >
> > > If you are using other dbserver, check your manual.
> > >
> > > ----- Original Message -----
> > > From: "Shaun" <shaun@xxxxxxxxxxxxxx>
> > > To: <php-db@xxxxxxxxxxxxx>
> > > Sent: Monday, February 16, 2004 10:45 PM
> > > Subject: Brick Codes
> > >
> > >
> > > > Hi,
> > > >
> > > > I have a table of Locations around the country. My system produces
> > reports
> > > > based on these Locations. I also have a table containing Brick Codes
> > e.g.
> > > >
> > > > Brick Post Code
> > > > AB51 AB51
> > > > AB52 AB52
> > > > AB55 AB55
> > > > AB56 AB56
> > > > AL01 AL1
> > > > AL02 AL2
> > > > AL03 AL3
> > > > AL04 AL4
> > > >
> > > >
> > > > How can I compare the first 3 or 4 letters of the postcode in the
> > > Locations
> > > > table to the corresponding entry in the Brick Codes table so I can
add
> > it
> > > to
> > > > my report?
> > > >
> > > > Thanks for your help
> > > >
> > > > --
> > > > PHP Database Mailing List (http://www.php.net/)
> > > > To unsubscribe, visit: http://www.php.net/unsub.php
> > > >
> > > >
> > >
> > > --
> > > PHP Database Mailing List (http://www.php.net/)
> > > To unsubscribe, visit: http://www.php.net/unsub.php
> > >
> > >
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
