SQL statement with spaces in the mysql query field

(1) mysqlreplace function Syntax: replace (object, search, replace) meaning: replace all search items in the object with the following replace code: update 'new' set 'content' replace ('content ',,); clear the spaces in the content field of the news table so that you can directly query with like. (2) mysql

(1) mysql replace function Syntax: replace (object, search, replace) meaning: replace all search objects with the following replace code: update 'new' set 'content' = replace ('content ','',''); // clear spaces in the content field of the news table so that you can directly query with like. (2) mysql

(1) mysql replace Function

Syntax: replace (object, search, replace)

Replace all search objects with replace

The Code is as follows:

Update 'news 'set 'content' = replace ('content', '',''); // clear the spaces in the content field of the news table so that you can directly query with like.

(2) mysql trim Function

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

The following is an example:

The Code is as follows:

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 'phpernotexxy ');

-> 'Phpernotex'

When we use SQL queries, if the value of this field in the database contains spaces (inside the string, not at the beginning or end), or the string we query contains spaces, the field does not contain spaces. We may not be able to find anything. Suppose there is a table below:

Table Name

Id url title content

There are spaces in the title of the first and second records in this table. If we do not know, or the content is not available, there is a great deal of uncertainty about the space:

The Code is as follows:

Select * from table where title = 'Li Yang technical blog ';
Select * from table where title like '% Li Yang technical blog % ';

The above two SQL statements cannot query the correct results. How should we write them?

See the following:

The Code is as follows:

Select * from table where trim (replace (title, '','') = trim (replace ('Li Yang technical blog ','',''));
Select * from table where trim (replace (title, '','') like trim (replace ('% Li Yang technical blog % ','',''));

Trim removes the spaces ending with the string, and replace removes the spaces inside the string.

In this way, the matching can be done correctly. If you do not want to put too much pressure on mysql, we can implement the processing of spaces in the condition part in the program.