update tablename set col1=case when col1 is null then '' else col1 end, col2=case when col2 is null then '' else col2 end, ... colN=case when colN is null then '' else colN end
update tablename set col1=isnull(col1,''), col2=isnull(col2,''), ... colN=isnull(colN,'')
DECLARE @table_name varchar(200),@col_name varchar(200)declare tutest_1 cursor forselect a.name, c.name from sysobjects a join sysindexes b on a.id=b.id right join syscolumns c on c.id=b.id --求字段的类型 join systypes d on d.xusertype=c.xusertype where b.indid in (0,1) and a.xtype='U' and d.name ='varchar'OPEN tutest_1FETCH NEXT FROM tutest_1 INTO @table_name, @col_nameWHILE @@FETCH_STATUS = 0 begin declare @sql varchar(8000)set @sql=''set @sql =+ @sql + 'update ' +@table_name + 'set ' + @col_name + 'replace('+@col_name+' ,' ','')'exec(@sql)if @@rowcount>0 print(@sql) FETCH NEXT FROM tutest_1 INTO @table_name, @col_name end CLOSE tutest_1 DEALLOCATE tutest_1
set col1=case when col1 is null then '' else col1 end,
col2=case when col2 is null then '' else col2 end,
...
colN=case when colN is null then '' else colN end
set col1=isnull(col1,''),
col2=isnull(col2,''),
...
colN=isnull(colN,'')
列名 'col8' 无效。
服务器: 消息 207,级别 16,状态 1,行 3
列名 'col8' 无效。
join sysindexes b on a.id=b.id
right join syscolumns c on c.id=b.id
--求字段的类型
join systypes d on d.xusertype=c.xusertype
where b.indid in (0,1)
and a.xtype='U'
and d.name ='varchar'OPEN tutest_1FETCH NEXT FROM tutest_1
INTO @table_name, @col_nameWHILE @@FETCH_STATUS = 0
begin
declare @sql varchar(8000)set @sql=''set @sql =+ @sql + 'update ' +@table_name + 'set ' + @col_name + 'replace('+@col_name+' ,' ','')'exec(@sql)if @@rowcount>0
print(@sql)
FETCH NEXT FROM tutest_1 INTO @table_name, @col_name
end
CLOSE tutest_1
DEALLOCATE tutest_1