PHP to update postcodes in a table with a space.

[mackin]

I have a table of Postcodes - some of them have no spaces between Outcode and Incode i.e LA138UT instead of LA13 8UT.

 

I need a php code to search the column for no spaced postcodes, and insert a space 3 characters from the end of the postcode string.

 

I have this code to insert the space

if (strpos($str, ' ')===false){ //no space
   $str = substr_replace($str, ' ', -3, 0); //insert space
}

and i can make it work one postcode at a time using a form, but I want to run the script and it will update them all.

 

Any advice ?

[mackin]

trying to do it like this but the update function isnt correct

if (strpos($row_update_postcodes['est_postcode'], ' ')===false){ //no space
   $row_update_postcodes['est_postcode'] = substr_replace($row_update_postcodes['est_postcode'], ' ', -3, 0); //insert space
}

mysql_select_db($database_contractors, $contractors);
$query_update_postcodes2 = "UPDATE hotels SET est_postcode = "$row_update_postcodes['est_postcode']" WHERE id = "$row_update_postcodes['id']")";

[The Little Guy]

The following code will update all fields that don't have a space, if it does have a space it will ignore them.

 

Note: May want to back up the table before you run this

 

update hotels set est_postcode = if(name regexp ' ', name, concat(substring(name, 1, (length(name)-3)), ' ', substring(name, -3)));

[mikosiko]

other alternative

 

UPDATE hotels SET est_postcode =  CONCAT(LEFT(est_postcode,char_length(est_postcode) -3),' ',SUBSTR(est_postcode,-3)) 
   WHERE INSTR(est_postcode,' ') = 0

[mackin]

Thank you both - I went with mikosiko's version and it worked perfectly