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 Edition Server w/20 CAL’s

Price: $641.99
Buy It Now
Microsoft SQL Server 2008 R2 Standard Server License key +5CAL FactorySealedPack

Price: $495
Buy It Now
Microsoft SQL Server 2012 Standard 10CAL +Server License Key Factory Sealed Pack

Price: $899
Buy It Now
Microsoft SQL Server 2012 Standard with SP3 - 25 CAL License - SAME DAY DOWNLOAD

Price: $700
Buy It Now
Lenovo Microsoft SQL Server 2014 - License - 1 User CAL - OEM

Price: $169.99
Buy It Now
Microsoft SQL Server Standard 2014 + 10 Cal

Current Price: $122.5
Current Bids: 8







Copyright 2017 - TSQLTutorials.com