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
|
