MySQL or MariaDB Command: How to Replace a String

Anyone who has to manage a MySQL or MariaDB database has at one time or another been presented with the need to recursively replace or delete a string within the rows of the database. Normally this sort of thing is done with scripts specially made to solve the problem, but there is also a much quicker way to do it.

It is important that before proceeding with the reading and application of this article, that you know what a database is, that you know how to get your hands on it, and that you make a safety backup. It is easy to run into mistakes that can wreak havoc using this system because you might be in a position to forget something or use wildcards improperly.

The article is intended as a reminder for those who already know how to get their hands inside a database using the appropriate tools and clients.

The command in question is REPLACE and in this case it can be combined with the UPDATE command. The generic syntax of the command is:

USE database_name;

UPDATE table_name set column_name=REPLACE(column_name,‘text to search’,‘new text’);

The command can also be followed by the WHERE clause which is used in this case to limit the number of records on which to perform the operation to those we really need.

To make the concept clearer, I will insert an explanatory example below. Let us suppose that we have accidentally inserted text in some rows of our database that begins with a space. If I then wanted to remove the space at the beginning of each row where it was inserted by mistake, I would have to write:

UPDATE table_name set column_name=REPLACE(column_name,' “,”) WHERE column_name LIKE “ %”;

In this case, the character to be searched for is the space and must be replaced with nothing, i.e. the two superscripts side by side without spaces. The WHERE clause in the example says that the substitution must only be made in rows that begin with a space. Pay attention to the fact that the percent character, used as a wildcard, does not follow the first quote in this case because we are not looking for all spaces within strings but only the space at the beginning of the string.

If anything is unclear to you, feel free to post it in the comments and I will be happy to answer any questions you may have. Be good and try not to make a mess!