DECLARE @fieldtype sysname SET @fieldtype='varchar'--删除处理空格 DECLARE hCForEach CURSOR GLOBAL FOR SELECT N'update '+QUOTENAME(o.name) +N' set '+ QUOTENAME(c.name) + N' = replace(' + QUOTENAME(c.name) + ','' '','''')' FROM sysobjects o,syscolumns c,systypes t WHERE o.id=c.id AND OBJECTPROPERTY(o.id,N'IsUserTable')=1 AND c.xusertype=t.xusertype AND t.name=@fieldtype EXEC sp_MSforeach_Worker @command1=N'?'
declare @col varchar(10) declare @str varchar(8000) declare mycur cursor for select sysobjects.name,syscolumns.name from syscolumns join systypes on systypes.xtype=syscolumns.xtype join sysobjects on sysobjects.id=syscolumns.id where sysobjects.xtype='U' and systypes.name in('NVARCHAR','varchar','varchar')open mycur fetch next from mycur into @table,@col WHILE (@@FETCH_STATUS = 0 ) BEGIN select @str='update '+@table+' set '+@col+'=rtrim('+@col+')' exec (@str) print @str fetch next from mycur into @table,@col END close mycur deallocate mycur
谢谢 happyflystone 这个好用duanzhi1984 你的我没有测试 ,谢谢各位朋友了
sp_MSforeach_Worker是什么存储过程。
declare @table varchar(10) declare @col varchar(10) declare @str varchar(8000) declare mycur cursor for select sysobjects.name,syscolumns.name from syscolumns join systypes on systypes.xtype=syscolumns.xtype join sysobjects on sysobjects.id=syscolumns.id where sysobjects.xtype='U' and systypes.name in('NVARCHAR','varchar','varchar') open mycur fetch next from mycur into @table,@col WHILE (@@FETCH_STATUS = 0 ) BEGIN -- select @str='update '+@table+' set '+@col+'=rtrim('+@col+')'
select @str='update '+@table+' set '+@col+'=replace('+@col+','''','''')' exec (@str) print @str fetch next from mycur into @table,@col END close mycur deallocate mycur ----replace
FROM stu
表中数据类型是VARCHAR,CHAR,NVARCHAR的字段值中的不可见字符。就是 表中有123 这个数据的话 显示的时候 会发现 他后面有很多个空格123 这样的,在表中把他删除了 ,在显示就是123这样了
cast(col as varchar)
SET @fieldtype='varchar'--删除处理空格
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT N'update '+QUOTENAME(o.name)
+N' set '+ QUOTENAME(c.name) + N' = replace(' + QUOTENAME(c.name) + ','' '','''')'
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND c.xusertype=t.xusertype
AND t.name=@fieldtype
EXEC sp_MSforeach_Worker @command1=N'?'
可以实现更新库中所有的表中凡是varchar的类型字段中的空格
再动态执行这样的语句
--test
declare @table varchar(10)
declare @col varchar(10)
declare @str varchar(8000)
declare mycur cursor for
select sysobjects.name,syscolumns.name from syscolumns join systypes on systypes.xtype=syscolumns.xtype
join sysobjects on sysobjects.id=syscolumns.id
where sysobjects.xtype='U' and systypes.name in('NVARCHAR','varchar','varchar')open mycur
fetch next from mycur into @table,@col
WHILE (@@FETCH_STATUS = 0 )
BEGIN
select @str='update '+@table+' set '+@col+'=rtrim('+@col+')'
exec (@str)
print @str
fetch next from mycur into @table,@col
END
close mycur
deallocate mycur
sp_MSforeach_Worker是什么存储过程。
declare @table varchar(10)
declare @col varchar(10)
declare @str varchar(8000)
declare mycur cursor for
select sysobjects.name,syscolumns.name from syscolumns join systypes on systypes.xtype=syscolumns.xtype
join sysobjects on sysobjects.id=syscolumns.id
where sysobjects.xtype='U' and systypes.name in('NVARCHAR','varchar','varchar')
open mycur
fetch next from mycur into @table,@col
WHILE (@@FETCH_STATUS = 0 )
BEGIN
-- select @str='update '+@table+' set '+@col+'=rtrim('+@col+')'
select @str='update '+@table+' set '+@col+'=replace('+@col+','''','''')'
exec (@str)
print @str
fetch next from mycur into @table,@col
END
close mycur
deallocate mycur ----replace