用空字符串替换整个数据库的空 varchar 字段 [英] Replace null varchar fields with empty string for whole db
问题描述
我正在为客户重建一个遗留程序......数据库设计得相对较好,我唯一不关心的是所有的 null varchar 值......(个人不关心提交这个我的客户端代码...)
I'm rebuilding a legacy program for a client ... the database is relatively well designed the only thing I don't care for is all the null varchar values ... (personally don't care for handing this in my client code ... )
我正在尝试用空字符串替换所有空 varchar 列......通常我会在我的导入脚本中执行此操作,但我不需要修改架构,因此我不打算执行导入脚本...
I'm trying to replace all null varchar columns with empty strings ... normally I would do this in my import script but I don't need to modify the schema so I'm not going to do an import script ...
想知道你们中的任何一个聪明的 SQL 家伙/女孩是否知道一种方法来 foreach(db 中的 varchar 列)用 '' 替换 null 有点东西?
wondering if any of you slick SQL guys /girls know a way to foreach (varchar column in db) replace null with '' kinda thing?
我知道一定有办法...... P.S.MSSQL 2008r2 数据库
I know there must be a way ... P.S. MSSQL 2008r2 DB
推荐答案
为此,您可以构建动态 SQL 语句:
To do this, you can build dynamic SQL statement:
declare @sSQl nvarchar(max)=''
SELECT
@sSQl=@sSQl+'UPDATE ['+TABLE_NAME+ '] SET ['+COLUMN_NAME+']='''''+' WHERE ['+COLUMN_NAME+ '] IS NULL'+CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%CHAR%'
exec sp_executesql @ssql
这篇关于用空字符串替换整个数据库的空 varchar 字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!