SQL Server删除字符串列空白字符常规函数无效如何处理

问题根源 #

你碰到的不是普通半角空格（ASCII 码值32），而是其他空白类字符，常见的包括不间断空格（ASCII 160，也叫NBSP）、制表符、换行符、全角空格等，这类字符不会被默认只处理半角空格的TRIM、REPLACE(column,' ','')识别，所以会出现替换失效的情况。

第一步：确认多余字符的类型 #

先取异常值末尾字符的ASCII码，定位具体字符类型，不同数据库的查询语句如下：

• SQL Server/MySQL/PostgreSQL通用查询（取'test1'后缀的异常值末尾字符编码）：

SELECT ASCII(RIGHT(你的列名, 1)) FROM 你的表名 WHERE 你的列名 LIKE 'test1%';

拿到编码后就可以对应处理，常见的异常空白编码如下：

• 9：制表符
• 10：换行符
• 13：回车符
• 160：不间断空格
• 12288：全角空格

第二步：对应处理方案 #

方案1：通用全空白清理（推荐） #

直接用正则删除所有类型的空白字符，不需要提前确认字符类型，适配大部分场景：

• SQL Server（2017及以上版本支持）：

SELECT REGEXP_REPLACE(你的列名, '\s', '') FROM 你的表名;

• MySQL：

SELECT REGEXP_REPLACE(你的列名, '[[:space:]]', '') FROM 你的表名;

• PostgreSQL：

SELECT REGEXP_REPLACE(你的列名, '\s', '', 'g') FROM 你的表名;

方案2：特定字符替换（性能更高） #

如果已经确认了异常字符的编码，直接对应替换即可，比正则执行效率更高，示例如下：

• 替换不间断空格（ASCII 160）：

-- SQL Server/MySQL
SELECT REPLACE(你的列名, CHAR(160), '') FROM 你的表名;

• 替换全角空格（ASCII 12288）：

-- SQL Server
SELECT REPLACE(你的列名, NCHAR(12288), '') FROM 你的表名;
-- MySQL
SELECT REPLACE(你的列名, CHAR(12288 USING utf8), '') FROM 你的表名;

• 同时替换多种异常空白可以嵌套REPLACE：

SELECT REPLACE(REPLACE(你的列名, CHAR(160), ''), CHAR(9), '') FROM 你的表名;

结果验证 #

处理后执行SELECT LEN(处理后的列值)验证，正常的'test1'长度应该为5，符合预期即可。

内容的提问来源于stack exchange，提问作者inspiredd