摆脱MySql中的空格的麻烦 [英] Trouble in getting rid of spaces in MySql

问题描述

我在MySql中有一列，其中包含产品说明.有时，说明中的单词之间包含一个空格，我想将其变成一个空格，所以我通过以下查询找到了它:

I have a column in MySql that contains a description of a products. Sometimes the description contain more the one space between words and I would like to turn it into one space so I've found it with this query:

 SELECT * FROM `database`.`PRODUCTS` 
 WHERE `PRODUCTS`.`description` LIKE '%  %'

，然后通过以下方式对其进行修复:

and then repaired it by:

 UPDATE `database`.`PRODUCTS` 
 SET `PRODUCTS`.`description` = REPLACE(`PRODUCTS`.`description`,'  ',' ')

但是它并不能消除所有的双精度空格！有些类型的特殊"空间带有(我怀疑)不同的ASCII码-0xA0,0xC2

But it doesn't remove all the double spaces! There are some kind of "special" spaces with (I suspect) different ascii code - 0xA0,0xC2

如何SELECT将其删除?

谢谢

推荐答案

这些不是特殊"空间-它们不是ascii代码-如果它们在您的数据库中，则您的代码中存在错误，因此会将它们放到那里

These are not 'special' spaces - they are not ascii codes - if they are in your database then you've got bugs in the code which put them there.

为什么不仅仅更换它们?

Why not just replace them?

....
SET PRODUCTS.description = REPLACE(
    REPLACE(
       REPLACE(PRODUCTS.description
             ,CHAR(160),' ')
           , CHAR(194), ' ')
       , '  ', ' ');

这篇关于摆脱MySql中的空格的麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！