Displaying NULL values as 'unknown' in SQL

Question

I have a table that has two columns; one for Name (datatype nvarchar), the other for ID (datatype int and allows null).

I am trying to display all data from the table including those with null values but I want the query result to display the null value as 'unknown'. I ran the following query:

Select Name, ID
Case
When ID is null then 'unknown'
When id is not null then (ID)
End
From table

The problem is I am getting this message:

Conversion failed when converting the varchar value 'unknown' to data type int

Answer 1

I guess you could cast your integers to a varchar
You can also use COALESCE instead of case when dealing with nulls.

 Select Name, COALESCE(CAST(ID AS VARCHAR(10)),'unknown') AS ID
 From table

Answer 2

You can use COALESCE function

Select Name, coalesce(convert(varchar(20),ID), 'unknown')
From table