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
– Ernestas Gruodis, Commented Jan 31, 2014 at 6:38
@ErnestasGruodis - Okay good idea, will follow from next project. But any suggestion for above query. — Java Curious ღ
– Java Curious ღ, Commented Jan 31, 2014 at 6:41

Community · Accepted Answer · 2017-05-23 11:49:48Z

2

See Mysql string replace

SET SQL_SAFE_UPDATES=0;

Set SQL_SAFE_UPDATE mode

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

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Jan 31, 2014 at 6:31

Praveen Prasannan

7,15310 gold badges53 silver badges71 bronze badges

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

5 Comments

Java Curious ღ Over a year ago

Shall I have to use WHERE order_address LIKE '%sep%' compulsory or not ?

Praveen Prasannan Over a year ago

"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

Java Curious ღ Over a year ago

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

Praveen Prasannan Over a year ago

stackoverflow.com/questions/14231278/…

Java Curious ღ Over a year ago

Whooa, great sir.. Query Solved. Thank You Praveen Sir. I have just Used set query your then update, it works.

Aziz Shaikh · Accepted Answer · 2014-01-31 06:32:41Z

0

You may use REPLACE, like this:

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

answered Jan 31, 2014 at 6:32

Aziz Shaikh

16.6k11 gold badges65 silver badges81 bronze badges

3 Comments

Java Curious ღ Over a year ago

Shall I have to use WHERE order_address LIKE '%sep%' compulsory or not ?

Aziz Shaikh Over a year ago

@JavaCuriousღ no, not compulsory. May improve the performance though.

Java Curious ღ Over a year ago

see stackoverflow.com/questions/21473829/…

Collectives™ on Stack Overflow

Replace Existing String with New In MySql Table

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related