up vote 1 down vote favorite
1

i want to replace ALL comma , into ,<space> in all address table in my mysql table.

For example,

+----------------+----------------+
| Name           | Address        |
+----------------+----------------+
| Someone name   | A1,Street Name |
+----------------+----------------+

Into

+----------------+----------------+
| Name           | Address        |
+----------------+----------------+
| Someone name   | A1, Street Name|
+----------------+----------------+

Thanks in advance.

share|improve this question
100% accept rate
feedback

1 Answer

up vote 3 down vote accepted 
UPDATE tabl SET Address = REPLACE(Address, ',', ', ')

Back up your table before trying it out!

You can read more about replace function here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

share|improve this answer
that all i need. thank you. – apis17 May 18 '10 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 '10 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 '10 at 12:33
2  
@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 '10 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 '10 at 14:35
show 2 more comments
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.