BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
You can use SQL to do everything from simple arithmetic through to complicated functions - this blog gives you the low-down!
- Formulae, Expressions and Calculations in SQL
- Arithmetic and Numerical Functions
- Working with text (including string functions)
- CASE WHEN statement
- Dealing with nulls - ISNULL, COALESCE and CASE (this blog)
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:
Here's an example, showing someone's first name for the table above:
-- show people's names
IsNull(FirstName, 'Not given') AS 'First name',
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:
Here's an example, returning someone's phone number by trying various columns in turn:
-- get valid phone number
'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:
-- show first name without null
WHEN FirstName is null THEN 'Not given'
END AS 'First name',
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!