SQLServer数据库表中的右截断字符清除教程

这里说的右截断字符，是指类型为char或varchar的字段中，最后一个双字节字符（如汉字）由于字段宽度不够被切断为只剩下1个字节的字符，这个字符会被显示为乱码。sql server 2000及更早的版本，碰到要保存的字符串长度大于字段长度时，会把字符串截成字段的长度再保存，这个过程会出现右截断字符。升级到新版本后，这些右截断字符仍然存在，会妨碍数据导入/导出、bcp in/out等操作，因此需要把它们清除掉。

清除右截断字符的sql脚本如下所示。运行该脚本后生会产生清除右截断字符的sql命令，而不是直接进行清除右截断字符的操作。在正式执行这些sql命令前，可以先评估核实一下。

declare @dbname varchar(128) = 'targetdb';
if not exists (select * from sys.databases where name=@dbname)
begin
  print 'error: database ''' + @dbname + ''' not exists.';
  return;
end;

set nocount on;
declare @tabname varchar(128), @colname varchar(128), @sql nvarchar(4000);
declare @count int;
if object_id('tempdb..#tmp', 'u') is not null
  drop table #tmp;

create table #tmp (tabname varchar(256), colname varchar(256));
set @sql = 'insert into #tmp ';
set @sql = @sql + 'select tabname=b.name, colname=a.name'
set @sql = @sql + ' from ' + @dbname + '.sys.columns a, '+ @dbname + '.sys.tables b'
set @sql = @sql + ' where a.object_id = b.object_id and b.is_ms_shipped=0'
set @sql = @sql + ' and a.system_type_id in (select system_type_id from '+ @dbname+ '.sys.types where name in (''char'', ''varchar''))';
--print @sql;
exec (@sql);
create clustered index index_tmp on #tmp (tabname, colname);

set @tabname = (select min(tabname) from #tmp);
while @tabname is not null
begin
  set @colname = (select min(colname) from #tmp where tabname=@tabname);
  while @colname is not null
  begin
    set @sql = 'select @count=count(*) from ' + @dbname + '.dbo.' + @tabname + ' ';
      set @sql = @sql + ' where ' + @colname + ' is not null';
      set @sql = @sql + ' and ' + @colname + ' <> ''''';
      set @sql = @sql + ' and right(' + @colname + ', 1) <> '' ''';
      set @sql = @sql + ' and cast(cast(right(' + @colname + ', 1) as varbinary) as varchar) = ''''';
    --print @sql;
    exec sp_executesql @sql, n'@count int output', @count output;
    if @count > 0
    begin
      set @sql = 'update ' + @dbname +'.dbo.' + @tabname + ' set ';
      set @sql = @sql + @colname + '= rtrim(cast(cast(' + @colname + ' as varbinary(8000)) as varchar(8000)))';
      set @sql = @sql + ' where ' + @colname + ' is not null';
      set @sql = @sql + ' and ' + @colname + ' <> ''''';
      set @sql = @sql + ' and right(' + @colname + ', 1) <> '' ''';
      set @sql = @sql + ' and cast(cast(right(' + @colname + ', 1) as varbinary) as varchar) = ''''';
      print @sql;
    end;
    set @colname = (select min(colname) from #tmp where tabname=@tabname and colname>@colname);
  end;
  set @tabname = (select min(tabname) from #tmp where tabname>@tabname);
end;
set nocount off;