Handling Null Values in SQL Server
From time to time, we may want to replace NULL values with some desired value when doing an sql select statement. Turns out that SQL Server makes this a simple task – and no IF statements are necessary! To do this, we use SQL Server's ISNULL function. This function takes two parameters. The first parameter is the name of the field that we expect may have a NULL value that we wish to replace. The second parameter represents the replacement value for the NULL value.
So the syntax for the ISNULL function is:
ISNULL ( possible_null_value_field, replacement_value )
To test the ISNULL function, lets create a table and add some data to it.
lname as varchar(50),
gender as varchar(20))
INSERT INTO tblEmployees (fname, lname, gender)
SELECT 'John', 'Brown', 'Male'
UNION ALL
SELECT 'Sara', 'Smith', 'Female'
UNION ALL
SELECT 'Harry', 'Thomas', NULL
UNION ALL
SELECT 'Jennifer', 'Smith', 'Female'
UNION ALL
SELECT 'Jada', 'Reynolds', NULL
After running the above query we should have a table that looks like this:
| fname | lname | gender |
|---|---|---|
| John | Brown | Male |
| Sara | Smith | Female |
| Harry | Thomas | NULL |
| Jennifer | Smith | Female |
| Jada | Reynolds | NULL |
As you can see we have a couple records with NULL values for the gender field. So if I wanted to replace these NULL values in my SQL Select statement with say…."Not Specified," we would use the ISNULL function in the following manner:
FROM tblEmployees
It will produce the following results:
| fname | lname | gender |
|---|---|---|
| John | Brown | Male |
| Sara | Smith | Female |
| Harry | Thomas | Not Specified |
| Jennifer | Smith | Female |
| Jada | Reynolds | Not Specified |
I hope this will prove useful to someone out there. Please comment if you have any queries. I will try responding as soon as i can.
Twitter
Buzz
Facebook
hi wats your myspace page
Great article very important information i found here r1hth5HB8Khtmz
@what does my name mean
Sorry, I don’t have a myspace page.
Hi, I’m very interested in Linux but Im a Super Newbie and I’m having trouble deciding on the right distribution for me (Havent you heard this a million times?) anyway here is my problem, I need a distribution that can switch between reading and writing in English and Japanese (Japanese Language Support) with out restarting the operating system.
I am sorry, but I am not too familiar with Linux distributions. I wish I could recommend the right distribution for you. A quick google search brought me to Wikipedia’s List of Linux Distributions. I found one in the list called Berry Linux which seems to be a prospect based on your requirements. I hope that helps.