Get startedGet started for free

Imputing missing values (II)

What if you want to replace missing values in one column with another and want to check the replacement column to make sure it doesn't have any missing values? To do that you need to use the COALESCE statement.

SELECT Shape, City, COALESCE(Shape, City, 'Unknown') as NewShape
FROM Incidents
+----------------+-----------+-------------+
| Shape          |  City     |  NewShape   |
+----------------+-----------+-------------+
| NULL           | Orb       | Orb         |
| Triangle       | Toledo    | Triangle    |
| NULL           | NULL      | Unknown     | 
+----------------+-----------+-------------+

This exercise is part of the course

Intermediate SQL Server

View Course

Exercise instructions

Replace missing values in Country with the first non-missing value from IncidentState or City, in that order. Name the new column Location.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Replace missing values 
SELECT Country, ___(___, ___, ___) AS Location
FROM Incidents
WHERE Country IS NULL
Edit and Run Code