Replace null varchar fields with empty string for

问题:

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 ... )

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 ...

wondering if any of you slick SQL guys /girls know a way to foreach (varchar column in db) replace null with '' kinda thing?

I know there must be a way ... P.S. MSSQL 2008r2 DB

回答1:

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

回答2:

Personally I think it's a bad idea to replace null with empty string.

But you asked for it, so here it is

UPDATE table1 SET field1 = '' WHERE field1 IS NULL

The other option is to solve it in your select statements:

SELECT coalesce(field1, '') as field1_excl_nulls FROM table1 

This will replace null in the output with empty strings, whilst still leaving the null inside the database.