Sqlserver 特殊字符替换_sqlserver 更新语句特殊字符

缘起

记录Sqlserver中字符串中有特殊字符，记录了一下解决的方法，希望对你有用。

ASCII码大全

地址1：传送门
地址2：传送门

解决方案

我是直接执行下面的前四句

--tab 9  回车13  换行10  空格32  ' " , : ; . 
update Inventoty set name=replace(name,char(9),'') 
update Inventoty set name=replace(name,char(10),'') 
update Inventoty set name=replace(name,char(13),'') 
update Inventoty set name=replace(name,char(32),'')
update Inventoty set name=replace(name,'''','')
update Inventoty set name=replace(name,'"','')
update Inventoty set name=replace(name,',','')
update Inventoty set name=replace(name,':','')
update Inventoty set name=replace(name,';','')
update Inventoty set name=replace(name,'.','')

--看ASCII码
print ASCII('a')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

遍历字符串输出字符的ASCII码

SET TEXTSIZE 0;  
-- Create variables for the character string and for the current   
-- position in the string.  
DECLARE @position INT, @string CHAR(8);  
-- Initialize the current position and the string variables.  
SET @position = 1;  
SET @string = 'New Moon';  
WHILE @position <= DATALENGTH(@string)  
   BEGIN  
   SELECT ASCII(SUBSTRING(@string, @position, 1)),   
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))  
   SET @position = @position + 1  
   END;  
GO  
1
2
3
4
5
6
7
8
9
10
11
12
13
14