PHP mySQL - replace some string inside string

that all i need. thank you. – apis17 May 18 at 3:22

@apis17, do realize that the above will make A1,<space>Street Name -> A1,<space><space>Street Name which might not be ideal (might then require you to scan for all the double spaces and replace them with single spaces) – Unreason May 18 at 12:22

@Unreason, It would be better to first replace commas with some strange marker (like @@#!@#$!) then replace all occurrences of marker plus space with comma and space, and then do the same with remaining markers. – Kamil Szot May 18 at 12:33

@Kamil, any special value algorithm is evil :) I left it open for the OP to comment if it is even an issue (maybe there are no ,<space> in the addresses, or having ,<space><space> is not an issue). If it needs to be addressed I then one approach is similar to what you are suggesting, but does not use special/strange marker; instead it would REPLACE(Address, ', ', ',') first and after that REPLACE(Address, ',', ', '). This would achieve the same in 2 queries, your suggestion would do it in 3. Your suggestion would update 2*n records (where n is the number of records with comma in them). – Unreason May 18 at 14:31

The shrink-expand approach would not need to update 2*n records, but only at most 2*n records. It would not solve the problem of ',<space><space>'. One more note - mysql checks if the new value is equal to old value in UPDATE and skips I/O if it is, but some DBMS don't and in those cases using WHERE conditions might help (in some cases for mysql it is faster too, depending on selectivity and indexes). – Unreason May 18 at 14:35