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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 17, 2004 10:19 AM
Subject: Re: [PHP-DB] 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" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > 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" <[EMAIL PROTECTED]>
> > To: "Shaun" <[EMAIL PROTECTED]>
> > Cc: "PHP DB" <[EMAIL PROTECTED]>
> > Sent: Tuesday, February 17, 2004 10:23
> > Subject: Re: [PHP-DB] 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" <[EMAIL PROTECTED]>
> > > To: <[EMAIL PROTECTED]>
> > > Sent: Monday, February 16, 2004 10:45 PM
> > > Subject: [PHP-DB] 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

Reply via email to