Home > SQL Server, T-SQL > SQL SERVER – COALESCE() T-SQL function @SQLSERVER

SQL SERVER – COALESCE() T-SQL function @SQLSERVER


COALESCE returns value of the first NON NULL expression with in the function arguments. This function can take N number of arguments. The data type of the return value is determined by the expressions in the argument with the highest data type precedence.

Situation 1: If all arguments in the function are NULL then the output will be NULL. But, at-least one of the expression should be typed null (ie. it should have some data type).

Situation 2: if arguments contains null and nonnull expressions then the output of function will be the first nonnull expression and the data type will be one the highest data type precedence of all expressions.

Experiment 1

SELECT COALESCE (NULL ,NULL ,NULL, NULL)

Output

Error Message: At least one of the arguments to COALESCE must be a typed NULL

So, By Editing the SQL Query as given below

SELECT COALESCE (NULL ,NULL ,NULL, CAST(NULL AS VARCHAR(1)))

Output is

NULL

Experiment 2

SELECT COALESCE (NULL,2,’A’,3)

Output

2

Because, 2 is the first nonnull value and the data type is int.

Experiment 3

SELECT COALESCE (1,2,3, 4, 12.456)

Output

1.000

Because, 1 is the first non null value. but, the highest data type precedence in all expressions is numeric. So, output is 1.000

Try it yourself

Execute the following SQL statements and put the output in the comment section.

SELECT COALESCE (10.00, NULL, NULL, NULL)
SELECT COALESCE (NULL, 10000.00, NULL, NULL)
SELECT COALESCE (NULL, NULL, 15000, 3)
SELECT COALESCE (NULL,2,’ABCDEF’,3)

Alternate method

We can also use CASE expression instead COALESCE.

COALESCE(expression1,…n) is equivalent to the following CASE expression.

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

ELSE expressionN

END

Further Reading ISNULL(), NULLIF()

Thanks for reading.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: