Tuesday, February 10, 2009

Handling NULL and Empty Values in SQL Server

There are many times were we need to handle NULL and "empty" values in SQL Server.
Though there is a difference between a NULL and an "empty" value.

In this example we will examine the above issue.

Consider the following table:

CREATE TABLE [dbo].[table_A](
[id] [int] NOT NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_table_A] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]

Let's populate the table with some data:

insert into table_A(id,name)
values (1,'A'), (2,'B'), (3,''), (4,NULL)

Note that the third record (id=3) contains an empty string for the column "name".
Also note that the fourth (id=4) record contains a NULL value for the same column.

Let's see how can we handle the two above "special" cases.

First of all if we select all the records from table_A we will get:

select id, name from table_A

Result:

ID|Name
-----------
1|A
2|B
3|
4|NULL

Then let's try to handle the record having the NULL value and set as a new value the string "NewValue" for the result set of our select statement.

SQL Server provides 2 functions for doing this; (i) the ISNULL; and (ii) the COALESCE.
Even though the two functions are quite similar, still they have some differences:

(1) ISNULL takes only two parameters as input; (a) the expression to be checked and (b) the replacement value

(2) COALESCE takes N parameters as input (N>=2). By having N expressions as input parameters it returns the first expression that IS NOT NULL. If only all expressions are NULL it then returns a NULL value. It is like a more enhanced version of ISNULL.

Let's try to handle the above scenario with these two functions:

--USE of ISNULL--
select id,ISNULL(name,'NewValue') from table_A

Result:

ID|Name
---------
1|A
2|B
3|
4|NewValue


--USE of COALESCE--
select id,COALESCE(name,'NewValue','NewValue2') from table_A

Result:

ID|Name
-------
1|A
2|B
3|
4|NewValue

Hmmm, we can see that even though for the record with id=4 we got the "NewValue" string instead of NULL, the record with id=3 still returned an empty string value.

In this case it seems that the ISNULL and COALESCE functions had no effect on the third record's result. This was expected because that record does not contain a NULL value but an empty value instead. An empty value cannot be considered as a NULL of course.

So, how do we deal with this? How can we handle empty string values?

Unfortunately there is not a "magic" formula for that. The only suggestion is not to use empty strings but use NULL values instead which can be handled by the ISNULL, NULLIF and COALESCE functions.

Additionally you can always create your own user-defined scalar-valued function that can deal with the issue of the empty string values. In my example, I created the following function and called it ISEMPTY:

CREATE FUNCTION ISEMPTY
(
-- Input Parameters
@input as varchar(250),
@newValue varchar(250)
)

-- Output parameter
RETURNS varchar (250)

AS
BEGIN

-- First handle the case where the input value is a NULL
Declare @inputFiltered as varchar(250)

set @inputFiltered=ISNULL(@input,'')

-- The main logic goes here
RETURN (case rtrim(ltrim(@inputFiltered)) when '' then rtrim(ltrim(@newValue)) else rtrim(ltrim(@inputFiltered)) end)

END
GO

My function takes as input two parameters; (a) the input string and (b) the replacement string.

Then by using a combination of the ISNULL function and the CASE statement it handles both NULL and EMPTY string values. Though, the above user-defined function just handles strings and not any other expressions.

Now if we try running the ISEMPTY function for the same example we get the following:

select id, dbo.ISEMPTY(name,'NewValue') from table_a

Result:

ID|Name
-------
1|A
2|B
3|NewValue
4|NewValue

Well, I guess that's it! :)

You can find more information regarding the abovementioned built-in SQL Server functions that deal with NULLs on the following MSDN Library links: ISNULL, NULLIF, COALESCE.

Enjoy!!
[Ads]
Check out my latest eBooks on SQL Server:
Administering SQL Server - Ebook Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

5 comments:

mohit said...

gr8 article . heads off to you not bcoz what u did but bcoz what u did is needed by me a lot. i am searching and hitting towards blackhole of web but not getting that particular star(ur article). at last i found my moon. thanks again my dear friend .

Grace said...

thank you very very much ... it is a new knowledge for me...

Fernando Olmos said...

The simple things in life are often the best.

Bikram kumar sahoo said...

Can you please mek me under stand why these two below statements are different ..


SELECT ISNULL(NULLIF('','')/*Returns NULL*/,'xyz')
SELECT ISNULL(NULL,'xyz')

Artemakis Artemiou [MVP] said...

The NULLIF returns NULL indeed for your example, however as NULLIF compared a single character (blank space), the ISNULL function replaces that one character with the first character of 'xyz'.

For example, if you run:

SELECT ISNULL(NULLIF('aaa','aaa'),'xyz')
SELECT ISNULL(NULL,'xyz')

You will see that the output is the same.

A way to overcome such issues is to use the COALESCE function:
SELECT COALESCE(NULLIF('',''),'xyz')