Replace Existing String with New In MySql Table

Question

I have table like this :

enter image description here

In that order_address field is there.

In that I have stored details of buyer / user i.e. Name + address + City + Pin /Zip Code + State + Country + mobile.

Between All that I have used sep as separator of each one. Now I want to replace all sep with some other character in whole table.

Suppose I want to replace sep with // in whole table.

So how to do that using query? I don't know about it so any suggestion for that.

Just a comment - why to use MySQL if it's commercially super expensive? Now I moved to JavaDB - also the syntax is a little bit different.. — Ernestas Gruodis, Jan 31 '14 at 06:38
@ErnestasGruodis - Okay good idea, will follow from next project. But any suggestion for above query. — Java Curious ღ, Jan 31 '14 at 06:41

score 2 · Accepted Answer · edited May 23 '17 at 11:49

2

SET SQL_SAFE_UPDATES=0;

Update order_table 
set order_address = REPLACE(order_address , 'sep', '//')
WHERE order_address LIKE '%sep%';

edited May 23 '17 at 11:49

Community

answered Jan 31 '14 at 06:31

Praveen Prasannan

Shall I have to use `WHERE order_address LIKE '%sep%'` compulsory or not ? – Java Curious ღ Jan 31 '14 at 06:34
"You don't necessarily have to add the WHERE LIKE clause at the end, because if the text to find isn't there the row won't be updated, but it should speed things up." – gmaggio – Praveen Prasannan Jan 31 '14 at 06:36
If i ll use above query it gives me an error `12:05:29 Update b2b.order_master set order_address = REPLACE(order_address , 'sep', '//') WHERE order_address LIKE '%sep%' Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect. 0.046 sec` – Java Curious ღ Jan 31 '14 at 06:36
http://stackoverflow.com/questions/14231278/error-error-code-1175-during-executing-update-command-on-table-using-mysql-wo – Praveen Prasannan Jan 31 '14 at 06:47
Whooa, great sir.. Query Solved. Thank You Praveen Sir. I have just Used set query your then update, it works. – Java Curious ღ Jan 31 '14 at 06:47

score 0 · Answer 2 · answered Jan 31 '14 at 06:32

0

You may use REPLACE, like this:

UPDATE table1
SET order_address = REPLACE (order_address, 'sep', '//')
WHERE order_address LIKE '%sep%'

answered Jan 31 '14 at 06:32

Aziz Shaikh

Shall I have to use `WHERE order_address LIKE '%sep%'` compulsory or not ? – Java Curious ღ Jan 31 '14 at 06:34
@JavaCuriousღ no, not compulsory. May improve the performance though. – Aziz Shaikh Jan 31 '14 at 06:36
see http://stackoverflow.com/questions/21473829/replace-existing-string-with-new-in-mysql-table?noredirect=1#comment32409977_21473829 – Java Curious ღ Jan 31 '14 at 06:40

2 Answers2