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.
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- Add a column with a default value to an existing table in SQL Server
- How to return only the Date from a SQL Server DateTime datatype
- How to check if a column exists in a SQL Server table
- What is the difference between varchar and nvarchar?
- Check if table exists in SQL Server
- What is the equivalent of 'describe table' in SQL Server?
- How can I list all foreign keys referencing a given table in SQL Server?
- SQL update query using joins
- How can I get column names from a table in SQL Server?
- Get size of all tables in database
COALESCEis ANSI, supported by Postgres, MySQL... The only caveat is that it doesn't necessarily perform as fast as native syntax.COALESCE, which has one important benefit overNVL: it does shortcut evaluation, whereasNVLalways evaluates both parameters. CompareCOALESCE(1,my_expensive_function)withNVL(1,my_expensive_function).COALESE()function is great and you can read about it on MSDOC> COALESCE-- 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".