Newbee Query - Search and Replace 1

[A15]

I just need to update a mysql database.
all I need is a query that looks at all tables and all colums

to replace a URL

Example:
I need to replace

http://www.example.com

with

http://www.domain.com

in all tables.

Your assistance will be greatly appreciated.

 

[ingresman]

You can use the mysql functions to locate strings and remove the by spliting them up with left and right style function. I published some code here a little while for someone but that was to limit field to 20 characters with ... if the exceeded 20 bytes.
What you need to do looks a litte more complex and I suppose comes down to if the domain value is embedded in a string or occupies a single column. If it does occupy a single column your query would be like:

update table set url_col = "[URL unfurl="true"]www.domain.com"[/URL] where url_col = "[URL unfurl="true"]www.example.com"[/URL]

You would have a query like the above for each domain to change.
If it's embedded it gets harder.
Is this a tidying up excersie or something that you might want to do on a regular basis?, if's a one off a much dirtier solution should suffice.

 

[A15]

Thanks ingresman,

This is a one time project. One of my clients is moving his website from a development environment to a production location and we need to remove all references to the previous test URL with the current URL.

I got two questions:
1- would I need to run the code on every individual table or can I reference the entire database somehow?

2- what is the URL is in a string? Can I make a query where it would look through all tables and rows?

 

[lgarner]

I'd probably consider dumping the database and search/replace on the text file.

 

[ingresman]

Point 1 - yes you have to name individual tables, it might be usefull sometimes of you could name the database and all tables in!
Point 2.
I think you might have 1 of two situations such as:
table1.url
which would have values like "

http://www.fred.com".

This should be easy enough to update using the syntax I showed you above.
The second situation might be:
tabl2.address
which might have values like:
"customer is

http://www.fred.com"

&
"send to

http://www.example.com"

To write an SQL statment to update table2 would mean something like
update table2 set address = left(upto the start of old) + newurl + "right(from end of old) to the end
where address like "%old%"
Now I suspect that your SQL is not massive so you have the options of having a play to get it to work (and we can help with that) or as lggarner suggests dump the DB, update using a text editior and then reload the data.
You decision will probabbly be around the size of the database, do you have the admin skills to unload and reload and if you have the confidence to do it.
I think the people here will be more than helpfull whatever route you take.

 

[A15]

Thank you all for the great advice.

I ended up exporting it out and using the replace function.

That made it kind of easy.

 

[LarrySteele]

Another option is to use MySQL's replace function:

update <table name>
set <column> = replace(<column>,<value to find>,<value to replace>)

In your situation:
update <table name>
set <column> = replace(<column>,'

http://www.example.com','www.domain.com')

Of course, you can only apply this on one table at a time, but it is available.

Glad you were able to get it sorted out otherwise.