Ispirer SQLWays Database Migration Software

Returning the first non-NULL expression

This chapter describes functions that return the first non-null expression (or replace NULL value) in various databases and their conversion by SQLWays.

Note. These functions differ from functions like IFNULL e.g. that check the first expression for NULL and return either second or third expression.

TABLE 67. Returning the first non-NULL expression

Database	Syntax	Description
Oracle	NVL (exp1, exp2)	Replaces NULL with the specified replacement value.   Returns the same type as exp1.   If exp1 is NULL, then NVL returns exp2. If exp1 is NOT NULL, then NVL returns exp1. The arguments exp1 and exp2 can have any data type.   If expressions' data types are different, then Oracle converts exp2 to the data type of exp1 before comparing them.
	COALESCE (exp1, exp2 [,expN]...)	Returns the first non-null expression in the expression list.   exp1...expN - are expressions of any data type   If all arguments are NULL, COALESCE returns NULL.
Microsoft SQL Server	ISNULL (exp1, exp2)	Replaces NULL with the specified replacement value.   Returns the same type as exp1.   If exp1 is NULL, then ISNULL returns exp2. If exp1 is NOT NULL, then ISNULL returns exp1. The arguments exp1 and exp2 can have any data type, but exp2 must have the same type as exp1.
	COALESCE (exp1, exp2 [,expN]...)	Returns the first non-null expression in the expression list.   exp1...expN - are expressions of any data type   If all arguments are NULL, COALESCE returns NULL. All expressions must be of the same type or must be implicitly convertible to the same type.
MySQL	IFNULL (exp1, exp2)	Replaces NULL with the specified replacement value.   Returns the same type as exp1.   If exp1 is NULL, then IFNULL returns exp2. If exp1 is NOT NULL, then IFNULL returns exp1.
	COALESCE (exp1, exp2 [,expN]...)	Returns the first non-null expression in the expression list.   exp1...expN - are expressions of any data type   If all arguments are NULL, COALESCE returns NULL.
IBM DB2	COALESCE (exp1, exp2 [,expN]...)	Returns the first non-null expression in the expression list.   exp1...expN - are expressions of any data type   If all arguments are NULL, COALESCE returns NULL.   The selected argument is converted, if necessary, to the attributes of the result.
	VALUE (exp1, exp2 [,expN]...)	Returns the first non-null exp in the expression list.   VALUE is a synonym for COALESCE.
Sybase Adaptive Server Anywhere	COALESCE (exp1, exp2 [,expN]...)	Returns the first non-null expression in the expression list.   exp1...expN - are expressions of any data type   If all arguments are NULL, COALESCE returns NULL.
	ISNULL (exp1, exp2 [,expN]...)	Returns the first non-null exp in the expression list.   ISNULL is a synonym for COALESCE.
Sybase Adaptive Server Enterprise	ISNULL (exp1, exp2)	Returns the first non-null expression in the expression list.   The arguments exp1 and exp2 can have any data type.   The data types of the expressions must convert implicitly, or must use the convert function.
	COALESCE (exp1, exp2 [,expN]...)	Returns the first non-null expression in the expression list.   exp1...expN - are expression of any data type   If all arguments are NULL, COALESCE returns NULL.

             

Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.