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 kind of thing was done with scripts specifically made to solve the problem but there is also a much faster way to do it.
It is important that before proceeding with reading and applying this article, that you are aware of what a database is, know how to get your hands on it and 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.
This article is made as a reminder to 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 used in conjunction 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 that we really need.
To make so that the concept is clearer, of continuation I insert an explanatory example. Suppose we have inserted by mistake in some lines of our database some text that begins with a space. If I wanted to remove the space at the beginning of every line in which it has been 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 or the two superscripts side by side without spaces. The WHERE clause in the example says that you must do the substitution only and exclusively in the rows that start with a space. Pay attention to the fact that the percentage character, used as a wildcard, in this case does not follow the first quote because we are not looking for all the spaces in the 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’ll be happy to answer any questions you may have. Be good and try not to make a mess!
