blocks|key|574022|text|要替换all+spaces，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|574023|UPDATE+`table`+SET+`col_name`+=+REPLACE(`col_name`,+'+',+'')|code-block|syntax|javascript|574024|要删除所有tabs字符，请执行以下操作：|574025|UPDATE+`table`+SET+`col_name`+=+REPLACE(`col_name`,+'\t',+''+)|574026|要删除所有new+line字符，请执行以下操作：|574027|UPDATE+`table`+SET+`col_name`+=+REPLACE(`col_name`,+'\n',+'')|574028|http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace|574029|要删除列的first+and+last+space(s)：|574030|UPDATE+`table`+SET+`col_name`+=+TRIM(`col_name`)|574031|http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim|574032|entityMap|0|LINK|mutability|MUTABLE|url|1^0|3|A|0|0|5|4|0|0|5|8|0|0|0|25|0|0|5|N|0|0|0|22|1|0^^$0|@$1|2|3|4|5|6|7|18|8|@$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1B|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1C|8|@$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|1F|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1G|8|@$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1J|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1K|8|@]|D|@$9|1L|A|1M|1|1N]]|E|$]]|$1|U|3|V|5|6|7|1O|8|@$9|1P|A|1Q|B|C]]|D|@]|E|$]]|$1|W|3|X|5|H|7|1R|8|@]|D|@]|E|$I|J]]|$1|Y|3|Z|5|6|7|1S|8|@]|D|@$9|1T|A|1U|1|1V]]|E|$]]|$1|10|3|-4|5|6|7|1W|8|@]|D|@]|E|$]]]|11|$12|$5|13|14|15|E|$16|T]]|17|$5|13|14|15|E|$16|Z]]]]

To replace <code>all spaces</code> :

<pre><code>UPDATE `table` SET `col_name` = REPLACE(`col_name`, ' ', '')
</code></pre>

To remove all <code>tabs</code> characters :

<pre><code>UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\t', '' )
</code></pre>

To remove all <code>new line</code> characters :

<pre><code>UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\n', '')
</code></pre>

<a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace</a>

To remove <code>first and last space(s)</code> of column :

<pre><code>UPDATE `table` SET `col_name` = TRIM(`col_name`)
</code></pre>

<a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim</a>

blocks|key|574075|text|工作查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|574076|SELECT+replace(col_name+,+'+','')+FROM+table_name;|offset|length|style|CODE|574077|而这不是：|574078|SELECT+trim(col_name)+FROM+table_name;|574079|entityMap^0|0|0|1E|0|0|0|12|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

Working Query:

<code>SELECT replace(col_name , ' ','') FROM table_name;</code>

While this doesn't :

<code>SELECT trim(col_name) FROM table_name;</code>

blocks|key|574149|text|使用以下查询，您可以删除MySQL中的前导空格和尾随空格。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|574150|UPDATE+`table_name`
SET+`col_name`+=+TRIM(`col_name`);|code-block|syntax|javascript|574151|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Using below query you can remove leading and trailing whitespace in a MySQL.

<pre><code>UPDATE `table_name`
SET `col_name` = TRIM(`col_name`);
</code></pre>

blocks|key|574259|text|因为问题是如何替换所有空格|type|unstyled|depth|inlineStyleRanges|entityRanges|data|574260|UPDATE+`table`+
SET+`col_name`+=+REPLACE
(REPLACE(REPLACE(`col_name`,+'+',+''),+'\t',+''),+'\n',+'');|code-block|syntax|javascript|574261|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Since the question is how to replace ALL whitespaces

<pre><code>UPDATE `table` 
SET `col_name` = REPLACE
(REPLACE(REPLACE(`col_name`, ' ', ''), '\t', ''), '\n', '');
</code></pre>

blocks|key|574330|text|注意:有两种类型的空格，一种来自空格键，另一种来自制表符按钮，您需要对这两种空格进行替换。我怀疑这就是TRIM函数似乎并不总是有效的原因。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|574331|所以|574332|replace(replace(<field_name>,'+',''),'++','')|code-block|syntax|javascript|574333|(可以有更多类型的空格，但这是我发现的两种类型)|574334|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Note: there are two types of white spaces, those that come from the space bar and those that come from the tab button, you need to do a replace for both. I suspect this is why the TRIM function seems not to always work.
so
<pre><code>replace(replace(&lt;field_name&gt;,' ',''),' ','')
</code></pre>
(there could be more types of white spaces, but these are the two I have found)

Is there a way to remove all whitespaces from a specific column for all values?

MySQL remove all whitespaces from the entire column

MySQL

有没有办法从所有值的特定列中删除所有空格？

问MySQL从整列中删除所有空格
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL从整列中删除所有空格EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL从整列中删除所有空格
EN