SQLUSA.com
SQL SERVER 2008 GRAND SLAM
FREE TRIAL  CLICK HERE TO ORDER  SEARCH
SQL JOBS SQL Server Training Scripts JOB BANK
  SQL Server 2008 Training Scripts  
  SQL Server 2005 Training Scripts  
News SQL Server Articles SQL Format

How to use COALESCE instead of ISNULL?

Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the use of the COALESCE function.

-- SQL coalesce - ANSI-92 SQL standard - SQL isnull - special in T-SQL

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address,

City,

Region=COALESCE(Region,''),

PostalCode, Country, Phone,

Fax=COALESCE(Fax, 'NO FAX')

FROM Northwind.dbo.Customers

ORDER BY CompanyName

GO

/* Partial results

 

City        Region      PostalCode  Country     Phone             Fax

Kobenhavn               1734        Denmark     31 12 34 56       31 13 35 57

Paris                   75016       France      (1) 47.55.60.10   (1) 47.55.60.20

Lander      WY          82520       USA         (307) 555-4680    (307) 555-6525

Charleroi               B-6000      Belgium     (071) 23 67 22 20 (071) 23 67 22 21

Portland    OR          97201       USA         (503) 555-3612    NO FAX

*/

------------

 

------------

-- SQL coalesce for comma-limited list building

------------

DECLARE  @EmpList VARCHAR(128);

SELECT   TOP 7 @EmpList =

  coalesce(@EmpList + ', ','') + cast(EmployeeID AS VARCHAR(4))

FROM     AdventureWorks.HumanResources.Employee

ORDER BY EmployeeID

 

SELECT EmployeeIDCSV = @EmpList

/* Result

 

EmployeeIDCSV

1, 2, 3, 4, 5, 6, 7

*/

------------

 

------------

-- Using coalesce in view definition

------------

-- SQL coalesce

-- SQL view\

-- SQL inner join

USE AdventureWorks

IF object_id(N'Person.vEmployeeName', 'V') IS NOT NULL

DROP VIEW Person.vEmployeeName

GO

CREATE VIEW Person.vEmployeeName

AS

SELECT FirstName,

MiddleName,

LastName,

Name=LastName + ', ' + FirstName + COALESCE(' ' + MiddleName, '')

FROM Person.Contact c

INNER JOIN HumanResources.Employee e

ON c.ContactID = e.ContactID

GO

 

SELECT * FROM Person.vEmployeeName

ORDER BY LastName, FirstName

GO

 

/* Partial results

 

FirstName   MiddleName  LastName    Name

Syed        E           Abbas       Abbas, Syed E

Kim         B           Abercrombie Abercrombie, Kim B

Hazem       E           Abolrous    Abolrous, Hazem E

Pilar       G           Ackerman    Ackerman, Pilar G

Jay         G           Adams       Adams, Jay G

François    P           Ajenstat    Ajenstat, François P

*/

------------

 

 

 

Order SQL 2008 GRAND SLAM Today!
SQLUSA.com Home Page
SQL Server Training at www.sqlusa.com.
SQL Server 2008 Video Training at www.sqlusa.com.
SQL Server 2005 Training Videos at www.sqlusa.com.
Microsoft SQL Server 2000 Training Videos at www.sqlusa.com.

FREE SQL & Business Intelligence / OLAP Short Videos on YOUTUBE.com

Microsoft Community Contributor 2011
Invest in Your SUCCESS!

Search SQLUSA FREE SQL Server Articles & FREE T-SQL Scripts


Copyright 2005-2011, SMI Corp. All Rights Reserved.

SQL Server 2012 is a program product of Microsoft Corporation.
SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.