GeekPhilosopher: Null Functions Sql Server tutorial

ISNULL:	Replaces an expression with a specified value if the expression is null.
Syntax:	ISNULL(check_expression, replacement_value)
Arguments:	check_expression: the expression to be checked for NULL. check_expression can be of any type. replacement_value: the expression to be returned if check_expression is NULL.

select isnull(middlename, 'none') as 'ISNULL', testNull.* from testNull

Results:

NULLIF

NULLIF:	Returns a null value when the two specified expressions are equal.
Syntax:	NULLIF(expression, expression )
Arguments:	expression: a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

select nullif(promotion1, promotion2) as 'NULLIF', testNull.* from testNull

Results:

COALESCE

COALESCE:	Returns the first nonnull expression from its arguments.
Syntax:	COALESCE(expression1, [ ,...n ] )
Arguments:	expression: An expression of any type. n: A placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

select coalesce(middlename, firstname) as 'COALESCE', testNull.* from testNull

Results: