Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in MS SQL Server

Introduction
The ISNULL() function
5. Using ISNULL() with aggregate functions
The COALESCE() function
The NULLIF() function
Summary

Instruction

Great! Did you know that you can use ISNULL() with aggregate functions? Check it out:

SELECT
  MIN(ISNULL(Price, 0.0))
FROM Product;

The above query calculates the lowest price among all products. If it finds a NULL in the Price column, it will display that product's price as zero. Therefore, this query returns 0.0 as the minimum value.

Without using ISNULL(), the minimum price would be 1.99. This is because aggregate functions don't take NULLs into account when they aggregate values.

Exercise

Let's find the number of characters in the shortest product name. Take NULLs into account by replacing all NULLs with the empty string (N''). Name the column MinLength.

Stuck? Here's a hint!

Use LEN() to find the length and MIN() to find the shortest. Use ISNULL() to change NULLs into empty strings.