142

In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL?

Goran
  • 1,744
  • 3
  • 12
  • 12

4 Answers4

157

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.)

Shannon Severance
  • 18,025
  • 3
  • 46
  • 67
  • 9
    +1: `COALESCE` is ANSI, supported by Postgres, MySQL... The only caveat is that it doesn't necessarily perform as fast as native syntax. – OMG Ponies Aug 19 '10 at 17:33
  • 20
    +1 for `COALESCE`, which has one important benefit over `NVL`: it does shortcut evaluation, whereas `NVL` always evaluates both parameters. Compare `COALESCE(1,my_expensive_function)` with `NVL(1,my_expensive_function)`. – Jeffrey Kemp Aug 20 '10 at 04:17
  • This `COALESE()` function is great and you can read about it on [MSDOC> COALESCE](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017)-- this same syntax works on Oracle. If your data has empty strings instead of NULLS you might need something like this: `COALESCE(TRIM(Tbl.myField1), TRIM(Tbl.myField2)) AS "myNewField"`. – SherlockSpreadsheets May 01 '19 at 16:33
142

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
BoltClock
  • 700,868
  • 160
  • 1,392
  • 1,356
44

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

MinhD
  • 1,790
  • 11
  • 14
10

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.

aledpardo
  • 761
  • 9
  • 19
FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202