Re: Brick Codes

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@mania.plus.com> To: <php-db@lists.php.net> 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" <ignatius.reilly@free.fr> wrote in message > news:06b901c3f53e$9c371960$0100a8c0@server... > > 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@elevaconsult.pt> > > To: "Shaun" <shaun@mania.plus.com> > > Cc: "PHP DB" <php-db@lists.php.net> > > 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" <shaun@mania.plus.com> > > > To: <php-db@lists.php.net> > > > 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 > >