去除指定表中数据类型是VARCHAR,CHAR,NVARCHAR的字段值中的不可见字符

SET NOCOUNT ON;

DECLARE @TblName VARCHAR(100);
DECLARE @UpdateString NVARCHAR(1000);
DECLARE @SelectString NVARCHAR(1000);
DECLARE @COlName VARCHAR(100);
DECLARE @COUNT INT;

SET @TblName = 'gl_accvouch'; --指定想要修改的表名

--定义游标取出指定表内的数据类型是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

DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 31
BEGIN
SET @SelectString = N'SELECT @COU=COUNT(*)
FROM ' + @TblName + N'
WHERE charindex(char(' + CAST(@i AS VARCHAR(2)) + N'),' + @COlName + N')>0';
EXEC sp_executesql @SelectString, N'@COU INT OUTPUT', @COUNT OUTPUT;

WHILE @COUNT > 0
BEGIN
SET @UpdateString
= N' UPDATE ' + @TblName + N' SET ' + @COlName + N'=ltrim(rtrim(REPLACE(' + @COlName
+ N',char(' + CAST(@i AS VARCHAR(2)) + N'),'' ''))) where charindex(char(' + CAST(@i AS VARCHAR(2))
+ N'),' + @COlName + N')>0';
PRINT @UpdateString;
EXEC sp_executesql @UpdateString;
EXEC sp_executesql @SelectString, N'@COU INT OUTPUT', @COUNT OUTPUT;
END;
SET @i = @i + 1;
END;
PRINT 'column: ' + @COlName + '－－－ok';
FETCH NEXT FROM cur_ColName
INTO @COlName;
END;
--关闭、释放游标
LABCLOSE:
CLOSE cur_ColName;
DEALLOCATE cur_ColName;