[RESOLVED] Trim from within SQL command?

djalecc

I've got a slight problem, I've already built up my database of users who have already entered their [ Post Code ] as one field. [d12]

I'm now in need of only using the first part of the post code in a MySQL query.

So if the post code in [ d12 ] = EC4 2XY I only want to pull EC4 from the table.

$query_userCoords = "SELECT COUNT(dest.outcode) as density, dest.outcode, dest.x, dest.y FROM drivers AS source, hwz_postcodes AS dest WHERE d12 = dest.outcode GROUP BY dest.outcode ASC";

The table is called [ drivers ] and the field is [ d12 ]

Can I change the above code to trim the result to just EC4 (assuming it was split up with a space, where some of the post codes have been entered without spaces!! ... not very helpful, but its a start.)

Just wondering if this is possible, and if so how can it be done in the above line?

If its not possible, I'll just have to re-build another field with the information I want sigh

NeedaCart

Im thinking that then end of the post code is always 3 characters yea!

So cut that out, then uses the remainder, and trim it up to clean up any spaces.

<?php
//NO THATS NOT MY POSTCODE!
$postcode = "SE20 5XS";
$trim = substr($postcode, 0, -3);
rtrim($trim);

echo $trim;
?>

esukf

Assuming there is a space, you can try

 $query_userCoords = "SELECT COUNT(dest.outcode) as density, SUBSTRING_INDEX(dest.outcode, ' ', 1) AS area_code, dest.x, dest.y FROM drivers AS source, hwz_postcodes AS dest WHERE d12 = dest.outcode GROUP BY dest.outcode ASC";

NeedaCart

he did say some of them dont have spaces

Installer

Wouldn't this work?

$query_userCoords = "SELECT COUNT(dest.outcode) AS density, dest.outcode, dest.x, dest.y " .
                    "FROM drivers AS source, hwz_postcodes AS dest " .
                    "WHERE LEFT(d12, 3) = dest.outcode GROUP BY dest.outcode ASC";

djalecc

Installer wrote:

Wouldn't this work?

$query_userCoords = "SELECT COUNT(dest.outcode) AS density, dest.outcode, dest.x, dest.y " .
                    "FROM drivers AS source, hwz_postcodes AS dest " .
                    "WHERE LEFT(d12, 3) = dest.outcode GROUP BY dest.outcode ASC";

Hey many thanks Installer (and everyone else)... Yours worked like a charm!

😃

ClarkF1

Some postcodes have four characters at the front so you need to be aware e.g. KT10

Installer

In that case, this might work (untested):

$query_userCoords = "SELECT COUNT(dest.outcode) AS density, dest.outcode, dest.x, dest.y " . 
                    "FROM drivers AS source, hwz_postcodes AS dest " . 
                    "WHERE TRIM(LEFT(d12, LENGTH(d12) - 3)) = dest.outcode " .
                    "GROUP BY dest.outcode ASC";