Imputing missing values (I)
In the previous exercise, you looked at the non-missing values in the IncidentState
column. But what if you want to replace the missing values with another value instead of omitting them? You can do this using the ISNULL()
function. Here we replace all the missing values in the Shape
column using the word 'Saucer'
:
SELECT Shape, ISNULL(Shape, 'Saucer') AS Shape2
FROM Incidents
You can also use ISNULL()
to replace values from a different column instead of a specified word.
This exercise is part of the course
Intermediate SQL Server
Exercise instructions
- Write a T-SQL query which only returns rows where
IncidentState
is missing. - Replace all the missing values in the
IncidentState
column with the values in theCity
column and name this new columnLocation
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Check the IncidentState column for missing values and replace them with the City column
SELECT IncidentState, ___ AS Location
FROM Incidents
-- Filter to only return missing values from IncidentState
WHERE ___ ___ ___