mackin Posted December 13, 2011 Share Posted December 13, 2011 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 ? Quote Link to comment Share on other sites More sharing options...
mackin Posted December 13, 2011 Author Share Posted December 13, 2011 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']")"; Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted December 13, 2011 Share Posted December 13, 2011 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))); Quote Link to comment Share on other sites More sharing options...
mikosiko Posted December 13, 2011 Share Posted December 13, 2011 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 Quote Link to comment Share on other sites More sharing options...
mackin Posted December 13, 2011 Author Share Posted December 13, 2011 Thank you both - I went with mikosiko's version and it worked perfectly Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.