mysql-去除字符串中的“空格”、“水平制表符”、“换行键”、“回车”

昨天需要在服务器的数据库上用Navicat导出数据，因为服务器上不能装Excel，所以只能将数据导出为txt文件，再将数据复制到Excel中;
但是在复制的过程中总是出现莫名其妙的换行和换列，看了下导出数据发现有的用户手动录入的数据中存在“水平制表符”、“空格”和“换行键”，在mysql中可以用replace函数将数据中的这些特殊字符去除掉，再进行导出，就可以解决上述问题了;

1. sql中的“水平制表符”、“换行键”、“回车”

char(9) \t 水平制表符
char(10) \r 换行键
char(13) \n 回车

2. mysql中REPLACE的基本语法

-- 将str中的from_str替换为to_str
REPLACE(str,from_str,to_str)

3. 需要同时替换多个字符类型时，可以将REPLACE进行嵌套

REPLACE(REPLACE(REPLACE(str,from_str,to_str),from_str,to_str),from_str,to_str)

SELECT
	f.NAME_ AS TYPE_,
	b.NAME_ AS BRAND_NAME_,
	a.NAME_ AS PROJECT_NAME_,
	REPLACE(a.ADDRESS_,CHAR(10),'') AS PROJECT_ADDRESS_,
	GROUP_CONCAT(d.NAME_) AS MANAGER_,
	REPLACE(REPLACE(a.CONTRACT_CODE_,' ',''),CHAR(9),'') AS CONTRACT_CODE_,
	a.MATERIAL_MONEY_,
	g.DISTRIBUTION_MONEY_,
	DATE_FORMAT(a.CREATE_DATE_,'%Y-%m-%d') AS CREATE_DATE_
FROM
	t_project_project a
JOIN t_product_brand b ON a.BRAND_TYPE_=b.ID_
LEFT JOIN t_project_manager c ON a.ID_ = c.PROJECT_
LEFT JOIN t_comm_user d ON c.MANAGER_=d.ID_
LEFT JOIN t_comm_dictionary f ON a.PROJECT_TYPE_=f.ID_
LEFT JOIN t_import_contract_detail g ON REPLACE(REPLACE(a.CONTRACT_CODE_,' ',''),CHAR(9),'')=g.CONTRACT_CODE_
WHERE
	a.PROJECT_TYPE_ IN (9700524, 9700622)
AND a.STATUS_ > 0
AND c.UPDATE_DATE_ is null
GROUP BY a.ID_
ORDER BY TYPE_,BRAND_NAME_,PROJECT_NAME_;

4. char码值对应列表

for (int i = 0; i < 133; i++) {
	char a = (char)i;
	Console.WriteLine("Char("+i+")----"+a);
}