What is the Oracle equivalent of SQL Server's IsNull() function?
In SQL Server we can type IsNull()
to determine if a field is null. Is there an equivalent function in PL/SQL?
coalesce
is supported in both Oracle and SQL Server and serves essentially the same function as nvl
and isnull
. (There are some important differences, coalesce
can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull
matches the type of the first argument, that is not true for coalesce
, at least on SQL Server.)
Instead of ISNULL()
, use NVL()
.
T-SQL:
SELECT ISNULL(SomeNullableField, 'If null, this value') FROM SomeTable
PL/SQL:
SELECT NVL(SomeNullableField, 'If null, this value') FROM SomeTable
Also use NVL2
as below if you want to return other value from the field_to_check
:
NVL2( field_to_check, value_if_NOT_null, value_if_null )
Usage: ORACLE/PLSQL: NVL2 FUNCTION
You can use the condition if x is not null then...
. It's not a function. There's also the NVL()
function, a good example of usage here: NVL function ref.