Share via

T-SQL Script to update string NULL with default NULL

  • Article

Problem

It is common to have nullable columns in a table but if we populate those nullable columns with string NULL instead of default NULL then a problem arises.

Effects of Problem:
**
**If we populate nullable columns with string column we cannot make use of NULL functions available in SQL Server.

For Example:

USE [AdventureWorks2012] 
GO 
--Create test table with two columns to hold string & default NULL 
CREATE TABLE  Test_Null(Id INT  IDENTITY(1,1),StringNull VARCHAR(10) ,DefaultNull VARCHAR(10)) 
INSERT Test_Null (StringNull) SELECT  'NULL'
INSERT Test_Null  SELECT  'NULL',NULL
  
--Execute below two queries to find how "IS NULL" works with string & default NULL 
SELECT * FROM Test_Null WHERE StringNULL IS NULL
SELECT * FROM Test_Null WHERE DefaultNull IS NULL
  
--Execute below two queries to find how "ISNULL" works with string & default NULL 
SELECT ISNULL(StringNULL,0) StringNULL FROM  Test_Null  
SELECT ISNULL(DefaultNull,0) DefaultNull FROM  Test_Null

Solution

USE [AdventureWorks2012] 
GO 
SET NOCOUNT ON
DECLARE @query NVARCHAR(MAX), 
@table_count INT, 
@column_count INT, 
@tablename VARCHAR(100), 
@Columnname VARCHAR(100), 
@Schemaname VARCHAR(100) = 'HumanResources', --schema names to be passed  
@i INT  = 1, 
@j INT  = 1 
DECLARE @MyTableVar TABLE(Number INT  IDENTITY(1,1), 
Table_list VARCHAR(200)); 
DECLARE @MyColumnVar TABLE(Number INT  IDENTITY(1,1), 
Column_list VARCHAR(200)); 
INSERT INTO  @MyTableVar 
SELECT name 
FROM sys.tables 
WHERE TYPE = 'U'  AND SCHEMA_NAME(SCHEMA_ID) = @Schemaname  
SELECT @table_count = MAX(Number) from  @MyTableVar 
WHILE @i <= @table_count 
BEGIN
SELECT @tablename = Table_list FROM @MyTableVar WHERE Number = @i 
INSERT @MyColumnVar 
SELECT C.name
FROM SYS.columns C 
INNER JOIN SYS.tables T  ON  T.object_id = C.object_id 
INNER JOIN SYS.types TY  ON  TY.user_type_id = C.user_type_id AND  TY.system_type_id = C.system_type_id 
WHERE SCHEMA_NAME(T.SCHEMA_ID) = @Schemaname  
AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
AND C.is_nullable = 1  
AND TY.system_type_id IN (167,175,231,239) --only character columns 
ORDER BY  C.column_id 
SELECT @column_count = MAX(Number) FROM  @MyColumnVar 
WHILE @j <= @column_count 
BEGIN
SELECT @Columnname = Column_list FROM @MyColumnVar WHERE Number = @j 
SET @query = 'UPDATE ['+@Schemaname+'].['+@tablename+'] SET ['+@Columnname+'] = NULL WHERE ['+@Columnname +'] = ''NULL''' + CHAR(10) + 'GO'
SET @j = @j + 1 
PRINT @query 
--To execute the generated Update scripts 
--EXEC (@query) 
END
SET @i = @i + 1 
END

*Note:

  • i) Above code will generate UPDATE scripts for tables that belong to the passed in schema names to the variable @Schemaname.
    ii) Above code will generate UPDATE scripts only for character columns (VARCHAR, CHAR, NVARCHAR).
    iii) Code is tested and working with SQL Server 2008 and SQL Server 2012.

See Also

Others Languages