MySQL

Search & Replace text in a MySQL Database

Let’s say you wanted do do a find-and-replace on a MySQL table column, something like updating a path in a field because you’ve modified where your images are located, which is something I’ve had to do lately.

You wouldn’t want to have to manually do this would you? Well you don’t have to, since you can use a combination of MySQLs replace function and an update statement. The syntax is as follows:-

UPDATE [table_name]
SET [field_name] =
replace([field_name],"[string_to_find]","[string_to_replace]");

That might take a bit of wrapping your head around the first time you use it. Here’s an actual example:-

UPDATE cms_members
SET profile_img =
replace(profile_img, "images/avatars/", "assets/images/profile/_small/");

So, in my example, I’m updating a MySQL table named “cms_members”, and searching and replacing the content of the “profile_img” column, replacing “images/avatars/” with “assets/images/profile/_small/”.

Leave a Reply

Your email address will not be published. Required fields are marked *