Click here to Skip to main content
Click here to Skip to main content

Using one Isnull() function to remove all null values from all columns a table in SQL Server

By , 14 Nov 2012
Rate this:
Please Sign up or sign in to vote.

Let's consider that we have a table that has several number of columns and they might have null values. Now we need to get blank or zero or anything else (whatever) in place of null values in the result set. For example let's have table (TEST) with data like: 

COL1

COL2

COL3

NULL

Kapil

NULL

TEST

NULL

NULL

And when we need a result like:

COL1

COL2

COL3 

A

Kapil

A

TEST

A

A

The SQL query would be-

Select IsNull(Col1,'A')as Col1, IsNull(Col2,'A')as Col2, IsNull(Col3,'A') as Col3 From TEST

But here we use three Isnull functions in the above query. But you have to write a SQL query (in SQL Server 2005 or above) in which there should be used only one ISNULL function (to avoid null values). And even query will be optimized if there will be less or no use of loop.

A solution will be like:

Declare @ColName as Varchar(400)
SEt @ColName=''
SELECT  @ColName= COALESCE( @ColName + ' ISNULL(' +c.name + ',''a'') ','', '') +
c.name + ', ' 
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
AND (o.name ='NULLDEMO')
SEt @ColName=  ( 'Select ' + SUBSTRING(@ColName,0,LEN(@ColName)-1) + ' FROM NULLDEMO ')
EXEC(@ColName)

Here, we getting all column names of the table using the sysobjects table then we create a SQL string (I did not use any loop) and then we create a query ...And execute it using EXEC.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

KapilDevTripathi

United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 PinmemberJohn B Oliver13-Dec-12 9:42 
GeneralMy vote of 3 [modified] PinmemberSpiff Dog15-Nov-12 7:43 
That is an interesting thought exercise, but finding things like this in a production environment when an isnull() would do the job simply and easily drives me a just little crazy.

modified 16-Nov-12 13:14pm.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140421.2 | Last Updated 14 Nov 2012
Article Copyright 2012 by KapilDevTripathi
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid