SQLServer数据库表中的右截断字符清除教程,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;
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。