This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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! 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) 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.

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!

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)

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

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!

Parts of this blog
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)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC