blocks|key|825067|text|在使用此解决方案之前，请先了解使用案例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|825068|执行select查询时trim不起作用|825069|这是可行的|825070|select+replace(name+,+'+','')+from+test;|code-block|syntax|javascript|825071|而这个不是|825072|select+trim(name)+from+test;|825073|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Please understand the use case before using this solution:

trim does not work while doing select query

This works

<pre><code>select replace(name , ' ','') from test;
</code></pre>

While this doesn't

<pre><code>select trim(name) from test;
</code></pre>

blocks|key|825138|text|您可以使用ltrim或rtrim来清除左右或字符串中的空格。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|825139|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

you can use ltrim or rtrim to clean whitespaces for the right or left or a string.

blocks|key|825183|text|如果需要在select查询中使用trim，也可以使用正则表达式|type|unstyled|depth|inlineStyleRanges|entityRanges|data|825184|SELECT+*+FROM+table_name+WHERE+field+RLIKE+'+*+query-string+*'|code-block|syntax|javascript|825185|返回带有字段的行，如“”*“”或“”query-string“。”|825186|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you need to use trim in select query, you can also use regular expressions

<pre><code>SELECT * FROM table_name WHERE field RLIKE ' * query-string *'
</code></pre>

return rows with field like '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;query-string&nbsp;&nbsp;&nbsp;'

blocks|key|825245|text|我从你的答案和其他链接中合成了一个通用的答案，它对我有效，我在评论中写道：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|825246|+UPDATE+FOO+set+FIELD2+=+TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r',''));|code-block|syntax|javascript|825247|等。|825248|因为trim()不会删除所有的空格，所以最好替换掉您想要的所有空格，而不是对其进行修剪。|825249|希望我能帮助你分享我的答案:)|825250|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

A general answer that I composed from your answers and from other links and it worked for me and I wrote it in a comment is:

<pre><code> UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r',''));
</code></pre>

etc.

Because trim() doesn't remove all the white spaces so it's better to replace all the white spaces u want and than trim it.

Hope I could help you with sharing my answer :)

blocks|key|610386|text|我需要修剪具有名字和姓氏的主键列中的值，所以我不想修剪所有空格，因为那样会删除名字和姓氏之间的空格，而我需要保留这些空格。对我有效的是..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|610387|UPDATE+`TABLE`+SET+`FIELD`=+TRIM(FIELD);|code-block|syntax|javascript|610388|或|610389|UPDATE+'TABLE'+SET+'FIELD'+=+RTRIM(FIELD);|610390|610391|UPDATE+'TABLE'+SET+'FIELD'+=+LTRIM(FIELD);|610392|请注意，第一个字段实例在单引号中，但第二个实例根本不在引号中。我必须这样做，否则它会给我一个语法错误，告诉我这是一个重复的主键，而我的两个主键都在引号中。|610393|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|T|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|K|3|H|5|6|7|V|8|@]|9|@]|A|$]]|$1|L|3|M|5|D|7|W|8|@]|9|@]|A|$E|F]]|$1|N|3|O|5|6|7|X|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|Q|$]]

I needed to trim the values in a primary key column that had first and last names, so I did not want to trim all white space as that would remove the space between the first and last name, which I needed to keep. What worked for me was...

<pre><code>UPDATE `TABLE` SET `FIELD`= TRIM(FIELD);
</code></pre>

or

<pre><code>UPDATE 'TABLE' SET 'FIELD' = RTRIM(FIELD);
</code></pre>

or

<pre><code>UPDATE 'TABLE' SET 'FIELD' = LTRIM(FIELD);
</code></pre>

Note that the first instance of FIELD is in single quotes but the second is not in quotes at all. I had to do it this way or it gave me a syntax error saying it was a duplicate primary key when I had both in quotes.

blocks|key|610414|text|您可以使用以下sql，UPDATE+TABLE+SET+Column=+replace(Column，‘'，'')|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|610415|entityMap^0|I|5|S|6|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

You can use the following sql,
UPDATE <code>TABLE</code> SET <code>Column</code>= replace(Column , ' ','')

I have a table with two fields (countries and ISO codes):

<pre><code>Table1

 field1 - e.g. 'Afghanistan' (without quotes)
 field2 - e.g. 'AF'(without quotes)
</code></pre>

In some rows the second field has whitespace at the start and/or end, which is affecting queries. 

<pre><code>Table1

 field1 - e.g. 'Afghanistan' (without quotes) 
 field2 - e.g. ' AF' (without quotes but with that space in front)
</code></pre>

Is there a way (in SQL) to go through the table and find/replace the whitespace in field2?

How to remove leading and trailing whitespace in a MySQL field?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个包含两个字段(国家/地区和ISO代码)的表：Table1   field1 - e.g. 'Afghanistan' (without quotes)   field2 - e.g. 'AF'(without quotes)在某些行中，第二个字段的开头和/或结尾有空格，这会影响查询。Table1   field...

问如何删除MySQL字段中的前导和尾随空格？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何删除MySQL字段中的前导和尾随空格？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何删除MySQL字段中的前导和尾随空格？
EN