怎样将sql数据库字段中的NULL都替换为空

大概想法是通过利用sys.columns这个系统表，然后组合语句之后执行。

declare @cmd varchar(MAX)declare @column varchar(MAX)declare @index intwhile 1 = 1 select top 1 @column = name, @index = column_id from sys.columns where column_id >@index and object_name(object_id) = 'Table_name'if @column is null breakselect @com = 'update Table_name set ' + @column + ' = '''' where ' + @column + ' is null'exec(@cmd)end

大概想法是通过利用sys.columns这个系统表，然后组合语句之后执行。

declare @cmd varchar(MAX)

declare @column varchar(MAX)

declare @index int

while 1 = 1

select top 1 @column = name, @index = column_id from sys.columns where column_id >@index and object_name(object_id) = 'Table_name'

if @column is null

break

select @com = 'update Table_name set ' + @column + ' = '''' where ' + @column + ' is null'

exec(@cmd)

end

里面的Table_name就是你的表名

在sqlserver中可以有几种方法：

---方法1:使用isnull替换

select keyId,isnull(info,0) as info from test

---方法2:使用case when 替换

select keyId,case when info is null then 0 else info end as info from test

---方法3:使用coalesce替换相应的值

select keyId , coalesce(info,0) as info from test

---