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
The COALESCE() function
10. Using COALESCE() with other functions
The NULLIF() function
Summary

Instruction

Nice! You can also use COALESCE() with other functions and operations, such as + concatenation, multiplication, etc. Take a look as we use + concatenation to show two columns as one: 

SELECT
  COALESCE(Category, N'unknown category')
  + ': ' 
  + COALESCE(Type, N'unknown type')
  AS CategoryType
FROM Product;

In the above query fragment, a NULL Category or Type value is replaced with an appropiate message, but we can still see the Type when the Category is NULL (and vice versa).

Do you remember how even one NULL in the whole expression will result in a NULL as an outcome for it? Thanks to COALESCE, this isn't a problem anymore.

Exercise

Show the following sentence:

Product X is made in Y.

X is the product Name and Y is the ProductionArea. If the name is not provided, write N'unknown name' instead. If the production area is not provided, write N'unknown area'. Name the column Origin.

Stuck? Here's a hint!

Use + to create the sentence. Remember about the period at the end of the sentence.