問題描述：在表列里有肉眼不可見字元，導致一些更新或插入失敗。 幾年前第一次碰見這種問題是在讀取考勤機人員信息時碰見的，折騰了一點時間，現在又碰到了還有點新發現就順便一起記錄下。 轉載註明出處：http://www.cnblogs.com/zzry/p/5729404.html 如下圖所示 golds ...
前言
問題描述：在表列里有肉眼不可見字元，導致一些更新或插入失敗。 幾年前第一次碰見這種問題是在讀取考勤機人員信息時碰見的，折騰了一點時間，現在又碰到了還有點新發現就順便一起記錄下。
轉載註明出處：http://www.cnblogs.com/zzry/p/5729404.html
如下圖所示 golds欄位 看上去5個字元，長度則為44 ，可判斷有特殊字元存在此列中。
基礎知識準備
貼幾個常用函數
1、獲取字元的ASCII碼 ASCII
ASCII碼是對字元的標準編碼。要獲取字元的ASCII碼就可以通過調用ASCII函數來實現。
語法結構：
ASCII(espression)
這裡的expression是一個返回char或varchar數據類型的表達式，ASCII函數僅對錶達式最左側的字元返回ASCII碼值。--這個是重點
返回值：int數據類型。
示例：
select ASCII('f') --輸出 102
註意如果是多個字元的字元串，也只是返回第一個字元的ASCII碼值。
2、獲取ASCII碼對應的字元 Char
語法結構：
char(integer_expression)
返回值類型：char型字元
示例：
select char(102) -- 輸出f
3、獲取字元的unicode編碼 Unicode
unicode函數可以獲得字元的unicode編碼。
語法結構：
unicode('ncharacter_expression')
這裡的ncharacter_expression是nchar或nvarchar類型的表達式。函數僅會返回第一個字元的unicode編碼。
返回值：int類型數據
示例：
SELECT Unicode('飛') -- 輸出 39134
4、獲取unicode編碼對應的字元nchar
nchar能夠根據unicode標準的定義，返回具有指定的整數代碼的unicode字元。
語法結構：
nchar(integer_expression)
返回值：unicode字元
示例：
SELECT nchar(39134) -- 輸出 飛
5、獲取字元串長度LEN
len函數用於獲取字元串的長度(字元數)，但不包括右邊的空格。左邊的空格和右邊的空格計算在內。
語法結構：
len( string_expression )
參數說明：
string_expression：要計算長度的字元串。
返回值：expression數據類型為varchar(max)、nvarchar(max)或varbinary(max)，則為bigint。否則為int。
示例：
select len('天下之大，無奇不有') -- 輸出 9
6、REPLACE
用另一個字元串值替換出現的所有指定字元串值。
語法如下：
REPLACE ( string_expression1 , string_expression2 , string_expression3 )
參數
string_expression1:要搜索的字元串表達式。string_expression1 可以是字元或二進位數據類型。
string_expression2:要查找的子字元串。string_expression2 可以是字元或二進位數據類型。
string_expression3:替換字元串。string_expression3 可以是字元或二進位數據類型。
SELECT REPLACE('abcde','abc','xxx') --xxxde
7 、指定位置搜索字元串中的內容CHARINDEX
charindex函數用於在指定的字元串中搜索特定的字元串，並可以指定開始搜索的位置，返回第一次找到目標字元串的字元數。
語法結構：
charindex ( expression1 , expression2 [ , start_location] )
參數說明：
expression1：一個字元串數據類型的表達式，其中包含要查找的字元的序列。
expression2：一個字元串數據類型的表達式，通常是一個為指定序列搜索的列。
start_location：開始在expression2中搜索expression1是的字元位置。如果start_location未被指定、是一個負數或零，則將從expression2的開頭開始搜索。 start_location可以是bingint類型。
返回值：如果expression2的數據類型為varchar(max)、nvarchar(max)或varbinary(max)，則為bigint，否則為int。
示例：
select charindex('456','123456789') -- 輸出 4 從123456789中檢索456出現的位置
select charindex('456','123456789',4) -- 輸出 4
問題處理
以前言中描述的例子為例（個人習慣處理方式供參考，如果你的肉眼自帶反隱功能可略過下文）
先查看左右邊字元和肉眼所見是否吻合結果如下圖
初步斷定不可見字元在右邊，因為第三個的查詢結果不是0而是空（可能是字元串空吧''）
轉載註明出處：http://www.cnblogs.com/zzry/p/5729404.html
接著查看它的ascii編碼值
查看ascii表（我是大自然的搬用工），ASCII 非列印控制字元：ASCII 表上的數字 0–31 分配給了控制字元，用於控制像印表機等一些外圍設備。
得到結論1：右邊第一個不可見字元是個控制字元（對應char(0),為什麼是char(0)上面貼的基礎知識有講）
一般到這裡就結束了，查到了是什麼字元，然後用replace函數替換成字元串空完事，下麵將其處理掉時發現失敗
上面查到了是char（0）在作祟，而且我迴圈輸出golds欄位右邊的字元發現有39個char（0），方便理解，簡化問題，改為下圖對比著看
初步斷定在@a里沒找到
繼續追蹤原因
原來如此，charindex都找不到它，得right一下才把這根刺挑出來。當是char（1）等等的時候是可以的，replace(@a,char(1) ,'a')
這樣是成功的，char（0）對應空字元，不對應資料庫的NULL哦。
既然這樣，那就用截取字元串的方法把需要的信息拿出來，框架大概如下，套個迴圈。當然也有可能有更好的方法，僅供參考
轉載註明出處：http://www.cnblogs.com/zzry/p/5729404.html
loop
UPDATE TblName SET
ColName = LEFT(ColName,LEN(ColName)-1)
WHERE ASCII(RIGHT( ColName ,1))=0
end loop
下麵共用個通用處理指定表中所有列中所有不可見字元的處理腳本，實測可用。
SET NOCOUNT ON
DECLARE @TblName VARCHAR(100)
DECLARE @UpdateString NVARCHAR(1000)
DECLARE @SelectString NVARCHAR(1000)
DECLARE @COlName VARCHAR(100)
DECLARE @COUNT INT
SET @TblName = '表名稱'--指定想要修改的表名
--定義游標取出指定表內的數據類型是VARCHAR,char,nVARCHAR的欄位名稱
DECLARE cur_ColName CURSOR
FOR
SELECT col.name
FROM syscolumns AS col
inner join sysobjects AS obj ON col.ID = obj.ID
INNER join systypes AS typ ON col.xtype = typ.xtype
WHERE obj.xtype ='U'
AND obj.name = @TblName
AND typ.name IN ('VARCHAR','CHAR','NVARCHAR','NCHAR')
FOR READ ONLY
--打開游標
OPEN cur_ColName
FETCH NEXT FROM cur_ColName INTO @ColName
IF @@FETCH_STATUS<>0
BEGIN
PRINT '沒有對應表或欄位，'PRINT '請確認當前資料庫內有' + @TblName + '表，' PRINT '或該表內有VARCHAR、CHAR、NVARCHAR、NCHAR類型的欄位！' GOTO LABCLOSE
END--迴圈修改
WHILE @@FETCH_STATUS=0
BEGIN
--拼修改字元串
--去掉左邊的不可見字元
SET @SelectString = 'SELECT @COU=COUNT(*)
FROM ' + @TblName +'
WHERE ASCII(LEFT(' + @ColName +',1))<32
AND '+ @ColName + ' IS NOT NULL'
EXEC sp_executesql @SelectString,N'@COU INT OUTPUT',
@COUNT OUTPUT WHILE @COUNT>0
BEGIN
SET @UpdateString =
' UPDATE ' + @TblName +
' SET ' + @ColName + '=RIGHT(' + @ColName + ',LEN(' + @ColName + ')-1)
WHERE ASCII(LEFT(' + @ColName + ',1))<32
AND ' + @ColName + ' IS NOT NULL'
EXEC sp_executesql @UpdateString
EXEC sp_executesql @SelectString,N'@COU INT OUTPUT',@COUNT OUTPUT
END
--去掉右邊的不可見字元
SET @SelectString = 'SELECT @COU=COUNT(*)
FROM ' + @TblName +'
WHERE ASCII(RIGHT(' + @ColName +',1))<32
AND '+ @ColName + ' IS NOT NULL'
EXEC sp_executesql @SelectString,N'@COU INT OUTPUT',
@COUNT OUTPUT WHILE @COUNT>0
BEGIN
SET @UpdateString = ' UPDATE ' + @TblName + ' SET '
+ @ColName + '=LEFT(' + @ColName + ',LEN(' + @ColName + ')-1)
WHERE ASCII(RIGHT(' + @ColName + ',1))<32
AND ' + @ColName + ' IS NOT NULL'
EXEC SP_EXECUTESQL @UpdateString
EXEC sp_executesql @SelectString,N'@COU INT OUTPUT',
@COUNT OUTPUT
END
PRINT 'column: ' + @ColName + '－－－ok'
FETCH NEXT FROM cur_ColName INTO @ColName
END
--關閉、釋放游標
LABCLOSE: CLOSE cur_ColName
DEALLOCATE cur_ColName
GO
