Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

Using the Transact SQL Coalesce Statement

The Transact SQL Coalesce function is an extremely useful piece of syntax to know as it can be used in place of other longer SQL Server syntax.

Given a number of arguments, the Coalesce function will return the first argument that does not have a value of null. Note that if all of the arguments are null, Coalesce itself will return null!

Incidentally, the case function can often be used instead of coalesce - see the article "Using the Transact SQL Case Statement".

Using the Coalesce Statement in Select Queries

The example below selects employee names and their telephone numbers from the SQL Server Northwind sample database. The coalesce function is used to return the first non-null value for a phone number as the PhoneNumber column. So if HomePhone is null, then the value of the Extension column will be returned instead. If the employee has neither a HomePhone or Extension then the value for PhoneNumber will be null.

SELECT LastName, FirstName, COALESCE(HomePhone, Extension) AS PhoneNumber FROM Employees

Useful Links

  • The SQL Documentation Tool builds technical documentation for Microsoft SQL Server databases. A free trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012