COALESCE Vs ISNULL Vs REPLACE

SQL Server has many functions which help developers in many ways. Today I am going to discuss only three functions which appear similar but are different!

Let's discuss them one by one!

1. COALESCE:

This came with MS SQL Server 2008. As per Microsoft following is the definition of this function:

COALESCE Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that is not null.

Basically, whenever you have multiple columns in a table or multiple values where you need to find the first NON-NULL value then you use this function. Following is the example:

SELECT COALESCE(NULL, NULL, 'Hello World')

Output: Hello World

NOTE:

• The data type of a COALESCE expression is the data type of the input argument with the highest data type precedence. If all inputs are the untyped NULL literal, you get an error.
• One apparent advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific. These differences between the two functions are fairly straightforward.

2.ISNULL:

This function also came with MS SQL Server 2008 and as per MS definition:

ISNULL replaces NULL with the specified replacement value.

Syntax:

ISNULL ( check_expression , replacement_value )

Example:

The following example finds the average of the weight of all products. It substitutes the value 50 for all NULL entries in the Weight column of the Product table.

USE AdventureWorks2012;

GO

SELECT AVG(ISNULL(Weight, 50))

FROM Production.Product;

GO

OUTPUT: 59.79

Furthermore, you can also replace a value if it's NULL. Please have a look at below example:

SELECT ISNULL(Department, 'TBD') From Department

This will replace department name with TBD whenever it's nULL in the Department Table.

3. REPLACE:

This function also came with MS Server 2008. Basically, whenever you need to replace a value in a string then it helps. Following is the formal definition provided by Microsoft:

Replaces all occurrences of a specified string value with another string value.

Syntax:

REPLACE ( string_expression , string_pattern , string_replacement )

Example :

The following example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx');

GO

Output: abxxxfghixxx

P.S. I hope this would be helpful for you. In case you have any feedback or you want to publish another article on MSBI/SQL Server, kindly post your comment.