Replace varchar null fields with empty string for integer db

Question

I am rebuilding an outdated program for the client ... the database is relatively well designed, the only thing I care about is all the null varchar values ​​... (I personally do not need to pass this into my client code ...)

I am trying to replace all columns of null varchar with empty rows ... usually I would do this in my import script, but I do not need to change the schema, so I will not do import script ...

wondering if any of you can retire, do the SQL guys / girls know the way for foreach (varchar column in db) to replace null with a “peculiar thing”?

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

Answer 1

To do this, you can build a 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 

Answer 2

Personally, I think it's nice to replace null an empty string.

But you asked him, so here it is

 UPDATE table1 SET field1 = '' WHERE field1 IS NULL 

Another 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 blank lines, while leaving zero inside the database.