Menu
I would like to write a select where I show the value of the field as normal except when the field is null. If it is null I'd like to show a special text, for example "Field is null". How would I best do this?
// Oracle newbie
867
Here is an example of how to use the Oracle IS NULL condition in a SELECT statement: SELECT * FROM suppliers WHERE supplier_name IS NULL; This Oracle IS NULL example will return all records from the suppliers table where the supplier_name contains a null value.
The value null represents the absence of any object, while the empty string is an object of type String with zero characters. If you try to compare the two, they are not the same.
NVL() The COALESCE() function is a part of SQL ANSI-92 standard while NVL() function is Oracle specific. Both statements return the same result which is one. However, the COALESCE() function only evaluates the first expression to determine the result while the NVL() function evaluates both expressions.
Answers. You could use the NVL statement to check for NULLs. Something like: WHEN NVL(column_value,'XXX') = 'XXX' THEN ...
I like to use function COALESCE for this purpose. It returns the first non-null value from given arguments (so you can test more than one field at a time).
SELECT COALESCE(NULL, 'Special text') FROM DUAL
So this would also work:
SELECT COALESCE(
First_Nullable_Field,
Second_Nullable_Field,
Third_Nullable_Field,
'All fields are NULL'
) FROM YourTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
