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.

Improve this question

1 Answer 1

Reset to default
4 
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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@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, 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, 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).
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 - I like your shrink/expand approach very much. It's way better than my magic marker idea.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.