WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 525 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

How to do calculations and expressions in T-SQL
Part five of a five-part series of blogs

You can use SQL to do everything from simple arithmetic through to complicated functions - this blog gives you the low-down!

1. Formulae, Expressions and Calculations in SQL
2. Arithmetic and Numerical Functions
3. Working with text (including string functions)
4. CASE WHEN statement
5. Dealing with nulls - ISNULL, COALESCE and CASE (this blog)

This blog is part of our full SQL online tutorial.  You can learn how to write SQL on a Wise Owl SQL classroom course if you're in the UK.

Posted by Andy Brown on 11 December 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Dealing with nulls - ISNULL, COALESCE and CASE

The final part of this blog looks at the thorny issue of nulls.  You can get one of these in a table by pressing CTRL + 0, although usually they're already there!

In this table of people, we've removed one person's first name.

There are 3 possible ways to deal with nulls in expressions: using IsNull, Coalesce or CASE.  I've explained these under separate headings below!

## The ISNULL Function

This function substitutes a given value when a column is null.  The syntax is:

IsNull(Expression which may be null, what to use instead)

Here's an example, showing someone's first name for the table above:

-- show people's names

SELECT

IsNull(FirstName, 'Not given') AS 'First name',

LastName

FROM

tblPerson

Here's what this would give for our table above:

SQL has substituted the words Not given when the first name is null.

## The COALESCE Function

This strangely-named function allows you to try multiple values.  The syntax is:

=COALESCE(First value which may be null, second value which may be null,  ... , last value to try)

Here's an example, returning someone's phone number by trying various columns in turn:

-- get valid phone number

COALESCE(

MobileNumber,

WorkPhone,

HomePhone,

'No phone number given'

) AS Phone

You can always use COALESCE instead of ISNULL, by just including two arguments for it.

## Processing nulls using CASE WHEN

This is my personal favourite, since it builds on something with which every SQL programmer should be familiar - the CASE statement (see previous part of this blog).  We could show the first name for the example at the start of this page as follows:

SELECT

FirstName,

-- show first name without null

CASE

WHEN FirstName is null THEN 'Not given'

ELSE FirstName

END AS 'First name',

LastName

FROM

tblPerson

This would give the same results:

The second column gives the first names of people, but with null values removed.

And that is the end of my blog on calculations in SQL!