MySQL batch removes spaces in a certain field

Is there any way to remove spaces from a certain field in batches in Mysql? Not only the spaces before and after the string, but also the spaces in the middle of the string. The answer is replace, using MySQL's own replace function. There is also a trim function.

(1) MySQL replace function

Syntax: replace(object,search,replace)

Meaning: Replace all occurrences of search in object with replace

Example: Clear spaces in the content field in the news table update `news` set `content`=replace(`content`,' ','');

(2) MySQL trim function

Syntax: trim([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

The following examples illustrate:

mysql> SELECT TRIM(' phpernote '); 
-> 'phpernote' 
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxphpernotexxx'); 
-> 'phpernotexxx' 
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxphpernotexxx'); 
-> 'phpernote' 
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'phpernotexxyz'); 
-> 'phpernotex' 

When inserting data into the MySQL database, due to negligence, there was an extra space in front of some lines of a column of text. Out of obsessive-compulsive disorder and to avoid possible problems in the future, I decided to remove this space. Most of the methods I found on the Internet are to use replace directly:

UPDATE example
SET col = replace(col, ' ', '')
WHERE col replace '^ ';

Although this method can remove the spaces at the beginning of the line, the spaces between the characters in the column used to separate words will also be removed, and a large piece of text will be connected, so it is not feasible.

Later, I wanted to see if I could use regular expressions to match when using replace. After searching for a while, I found that it was not feasible.

Finally, I thought of a method that I think is relatively simple and feasible:

First use CONCAT() to add an 'x' character to the front of the lines that have spaces:

UPDATE example
SET col = CONCAT('x', col)
WHERE col REGEXP '^ ';

This adds two characters 'x' to the front of the line.

Then use REPLACE to remove the two extra characters together:

UPDATE example
SET col = REPLACE(col, 'x ', '')
WHERE col REGEXP '^x ';

Summarize

The above is all about how to remove spaces from a certain field in MySQL in batches. I hope it will be helpful to you. Interested friends can refer to: MySQL declaration of variables and stored procedure analysis, several relatively important MySQL variables, MySQL database development specifications [recommended], etc. If you have any questions, please leave a message at any time and we can discuss together.