In SQL Server, the COALESCE
expression is used to handle NULL
values.
If the expression is null, then returns a default value.
During the evaluation of the expression, the user-defined values take the place of the NULL values.
A non-null result is always returned from an argument list that has been evaluated using SQL COALESCE expression.
In order to prevent having your results ruined by null values, it's imperative that you learn how to deal with missing
values in your datasets. You should know how they affect computations, queries, reports, and data-set planning.
Properties of the SQL Coalesce
The expressions must all use the same data types.
It can have more than one expression.
Using SQL's Coalesce expression is a shortcut for using the Case expression.
When an integer and a character expression are used together, the result is always an integer.
COALESCE Syntax:
COALESCE ( expression, ...n_value )
COALESCE Example:
SELECT COALESCE(null,11) as Value;
Value |
---|
11 |
Using COALESCE with character string data
In the following example, the string "Hi" is returned because it is the first non-null argument.
SELECT COALESCE(NULL, 'Test 1', 'Test 2', NULL);
SELECT COALESCE(NULL, NULL, 'Test 2', NULL);
See also: T-SQL Functions -> HOST_ID | HOST_NAME | ISNULL | ISNUMERIC | NULLIF