Replace a string in MySQL column using insert or update

Question

I am trying to replace a string like "example.com" with "newdomain.com" in a MySQL table called "wp_options" with a column name "option_value".

I have seen a lot of other questions about replacement but the problem is that they are all using the REPLACE function, which damages the structure of my table because it removes the row (if matches) and inserts a new one(1), which makes the primary key and unique id disappears (for example, this is causing me to lose my theme configuration).

Is there a way to replace such string using the INSERT function? or the UPDATE?

Those solutions do not use the REPLACE() function. They are using the REPLACE statement. The two are quite different. — Gordon Linoff
– Gordon Linoff, Commented Oct 15, 2017 at 11:59

Mureinik · Accepted Answer · 2017-10-15 09:17:37Z

1

A simple update statement should do it:

UPDATE wp_options
SET    option_value = 'newdomain.com'
WHERE  option_value = 'example.com'

EDIT:
If the requirement is to search within the value as clarified by the comment below, you can use the replace function:

UPDATE wp_options
SET    option_value = REPLACE(option_value, 'example.com', 'newdomain.com')
WHERE  option_value LIKE '%example.com%'

edited Oct 15, 2017 at 9:17

answered Oct 15, 2017 at 9:07

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

2 Comments

Madno Over a year ago

But this doesn't search for the string in all the column, it just replaces it if it equals example.com, what I am asking for is to search and replace as a string (I mean, even if i have something like example.com/posts/something, it should replace it here too).

Mureinik Over a year ago

@Madno I misunderstood the question then. See my edited answer for a solution that should accommodate this requirement.

Collectives™ on Stack Overflow

Replace a string in MySQL column using insert or update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related