how to replace null (number) to 'unknown' (char) in SQL?

Question

How to replace all null values in Grade1 to 'Unknown' in SQL? This didn't work because Grade1 is number but 'Unknown' is char.

SELECT NVL(Grade1, 'Unknown')
FROM table;

[table]

Student   Grade1  Grade2
1          NULL     0.88
2          0.80     0.50
3          NULL     0.40

Answer 1

You can't do just what you want. Both branches of NVL() must return the same datatype. If you really want "Unknown" for NULL values, then you have to convert non-NULL values to strings as well.

Assuming that you are running Oracle, as NVL() suggests:

NVL(TO_CHAR(grade1), 'Unknown')

That said, I would rather keep NULL values as they are. This seems like the right way to represent the absence of data (NULL really stands for "Unknown").

Answer 2

Assuming that your intention is to have the Grade1 column in the result be a varchar2

coalesce( to_char( Grade1 ), 'Unknown' )

You could use nvl rather than coalesce but coalese is more flexible (it can take more than two parameters) and more portable across different databases.

Answer 3

You can try this:

SELECT DECODE(Grade1, null, 'UnKnown', Grade1) FROM tbl;