SQL Server清除数据中的回车符、换行符、制表符

 

 

转载于：SQL Server清除数据中的回车符、换行符、制表符

在SQL Server中，回车符的AASCII码是13，换行符的ASCII码是10，制表符t的ASCII码CHAR(9)
1，回车符
查找有回车符的内容

select id,theme from TableName where charindex(char(13),theme) > 0

清除回车符

update TableName set theme=replace(theme, char(13),'') where 1=1

2，换行符
查找有换行符的内容

select id,theme from TableName where charindex(char(10),theme) > 0

清除换行符

update TableName set theme=replace(theme, char(10),'') where 1=1

3，制表符t
查找有制表符的内容

select id,theme from TableName where charindex(char(9),theme) > 0

清除制表符

update TableName set theme=replace(theme, char(9),'') where 1=1

4，回车换行符
查找有回车换行符的内容

select id,theme from TableName where charindex(char(13) + char(10),theme) > 0

清除回车换行符

update TableName set theme=replace(theme,char(13) + char(10),'') where 1=1