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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.