Posted by on in iDevelop | 52 comments

The Webmaster’s Weekly #12 is out & the Quick Tip tells you about a simple way of doing a search-replace in a MySQL table.

Well, it might be something new for Chrispian but I’ve been doing this for ages, infact I don’t know of any other search-replace technique for MySQL when you don’t need to replace the data of the whole column of a record, but just some text from between it, like in “hello world, today is wednesday” you want to replace “today is wednesday” with “yesterday was thursday”. This is ofcourse quite a simple example which can be done with a simple SQL. The tip however says that its quite simple to just download the data as SQL using phpMyAdmin, open it in a text editor, search-replace and then upload back after saving.

But being someone who’s done this way too many times & still does it, I wouldn’t recommend doing it to anyone unless the data you’ll be downloading is not too much and if there’s no easy way of doing it via an SQL UPDATE. The reason is why I prefer SQL in this case is because the data I work with, in which I’ve to perform search-replace, is always more than 10MB usually. So ofcourse downloading is a problem & then uploading is also a problem as I’ll have to split it into files each weighing less than 2MB(phpMyAdmin won’t upload files bigger than that, its the default max size allowed by PHP). 🙁 Quite a headache really!! :dizzy: On the otherhand, doing an SQL won’t take much time, and even writing the SQL would take up a bit of less time than downloading & uploading, and the additional benefit is that you get more fluent in SQL, so the next time it’ll be easier to do this. 😉

This is why, I wouldn’t recommend doing this download-search-replace-upload technique to anyone if using an SQL UPDATE is an option!! 😀