T-SQL Tutorials - TSQLTutorials.com

COALESCE - Replacing NULL values


Introduction

Often when writing queries you may want to check for NULL values and replace them with a non-NULL value. The COALESCE function is a good canidate to use for this purpose. It allows you to check if a value is NULL and then replace that value with a static value, or with another column(s). The following are two example syntaxes of using the COALESCE Function:

  • SELECT COALESCE([column1],'String') FROM table

  • SELECT COALESCE([column1],[column2],'String') FROM table



Examples

An example of using the COALESCE function would be to check a column for a NULL value and replace that NULL value with a value. In this example we can check a field called 'LastUpdateDate', which contains the last time a table record has been updated. If the record has never been updated the column will contain a NULL value. Using COALESCE we can replace the value with a default date
SELECT COALESCE([LastUpdateDate],'1/1/1900') AS [LastUpdateDate]
FROM UserTable

Another example of using the COALESCE function would be to check two related columns and use the first available value (NON-NULL value) from those columns to populate a new column. This time we will use the same 'LastUpdatedDate' column, but we will also use another column in the table called 'CreateDate', which contains the value that the record in the table was created. For this example we will assume that the CreateDate will always be populated. So for our query we will use the LastUpdateDate, unless it's value is NULL, otherwise we will use the CreateDate.
SELECT COALESCE([LastUpdateDate],[CreateDate]) AS [LastUpdateDate]
FROM UserTable

A final example of using the COALESCE function would be to check multiple related columns and use the first available value (NON-NULL value) from those columns to populate a new column. This time we will use the same 'LastUpdatedDate', and 'CreateDate' column, but this time we will assume that the 'CreateDate' column can also contain a NULL value. So for our query we will use the LastUpdateDate, unless it's value is NULL, otherwise we will use the CreateDate. If the CreateDate is NULL, then we will use a default date
SELECT COALESCE([LastUpdateDate],[CreateDate],'1/1/1900') AS [LastUpdateDate]
FROM UserTable


MICROSOFT SQL SERVER 2014 STANDARD W/5 CAL’s

Price: $249.99
Buy It Now
Microsoft SQL Server 2014 Standard Edition Server R2 w/20 CAL’s

Price: $641.99
Buy It Now
MSFT SQL Server 2016 Standard Edition x64 64bit | 10 CAL USERS Edition

Price: $499.99
Buy It Now
SQL Server 2014 Standard Edition, Media & License | 32 bit & 64 bit | 10CAL

Price: $399.99
Buy It Now
MSFT SQL Server 2012 Standard Edition, 32bit & 64bit, 16 Core Unlimited User CAL

Price: $999.99
Buy It Now
Microsoft SQL Server 2012 Standard +Microsoft USB +|10 or 25CAL Certificate| NEW
Price: $929
Buy It Now







Copyright 2017 - TSQLTutorials.com