0

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

Improve this question
1
  • That's because you can't mix datatypes in a single column. The way your case expression is written is that when ID is null you want a varchar but otherwise you want an int. This seems like it is likely for display, which really should be left to the front end. If you have to do this in sql you will have to cast your int to a varchar. Commented Jan 29, 2016 at 19:37

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Perfect. Just what I was looking for. Thanks for the help.
1

You can use COALESCE function

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.