SQL Server/T-SQL Tutorial/Data Convert Functions/COALESCE

A SELECT statement that substitutes a different data type

5> create table Bankers(
6>    BankerID             Integer,
7>    BankerName           VARCHAR(20),
8>    BankerContactLName   VARCHAR(20),
9>     BankerContactFName   VARCHAR(20),
10>     BankerCity           VARCHAR(20),
11>     BankerState          VARCHAR(20),
12>     BankerZipCode        VARCHAR(20),
13>     BankerPhone          VARCHAR(20)
14>  )
15>  GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (3, "HJI Inc.","Kit","Cat",  "Paris",    "CA","33333","333-333-3333");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina",   "ER","44444","444-444-4444");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (5, "RTY Inc.","Wil","Lee",  "Toronto",  "YU","55555","555-555-5555");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary",  "TY","66666","666-666-6666");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (7, "OIP Inc.","Yam","Act",  "San Franc","FG","77777","777-777-7777");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (8, "SAD Inc.","Hit","Eat",  "Orland",   "PO","88888","888-888-8888");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (9, "DFG Inc.","Sad","Lee",  "Wisler",   "PL","99999","999-999-9999");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (0, "GHJ Inc.","Bit","Lee",  "Ticker",   "MN","00000","000-000-0000");
4> GO
(1 rows affected)
1>
2>
3>
4> create table Billings (
5>     BankerID           INTEGER,
6>     BillingNumber      INTEGER,
7>      BillingDate        datetime,
8>      BillingTotal       INTEGER,
9>      TermsID            INTEGER,
10>      BillingDueDate     datetime ,
11>      PaymentTotal       INTEGER,
12>      CreditTotal        INTEGER
13>
14>  );
15>  GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
4> GO
(1 rows affected)
1>
2>
3>
4> SELECT BankerName,
5>     COALESCE(CAST(BillingTotal AS varchar), "No Billings") AS BillingTotal
6> FROM Bankers LEFT JOIN Billings
7>     ON Bankers.BankerID = Billings.BankerID
8> ORDER BY BankerName
9> GO
BankerName           BillingTotal
-------------------- ------------------------------
ABC Inc.             165
DEF Inc.             165
DFG Inc.             165
GHJ Inc.             165
HJI Inc.             165
OIP Inc.             165
QWE Inc.             165
RTY Inc.             165
SAD Inc.             165
YUI Inc.             165
(10 rows affected)
1> drop table Billings;
2> GO
1>

A SELECT statement that uses the COALESCE function

7>
8> create table Billings (
9>     BankerID           INTEGER,
10>     BillingNumber      INTEGER,
11>     BillingDate        datetime,
12>     BillingTotal       INTEGER,
13>     TermsID            INTEGER,
14>     BillingDueDate     datetime ,
15>     PaymentTotal       INTEGER,
16>     CreditTotal        INTEGER
17>
18> );
19> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2> SELECT BillingDate,
3>     COALESCE(BillingDate, "1900-01-01") AS NewDate
4> FROM Billings
5> GO
BillingDate             NewDate
----------------------- -----------------------
2005-01-22 00:00:00.000 2005-01-22 00:00:00.000
2001-02-21 00:00:00.000 2001-02-21 00:00:00.000
2003-05-02 00:00:00.000 2003-05-02 00:00:00.000
1999-03-12 00:00:00.000 1999-03-12 00:00:00.000
2000-04-23 00:00:00.000 2000-04-23 00:00:00.000
2001-06-14 00:00:00.000 2001-06-14 00:00:00.000
2002-07-15 00:00:00.000 2002-07-15 00:00:00.000
2003-08-16 00:00:00.000 2003-08-16 00:00:00.000
2004-09-17 00:00:00.000 2004-09-17 00:00:00.000
2005-10-18 00:00:00.000 2005-10-18 00:00:00.000
(10 rows affected)
1> drop table Billings;
2> GO
1>

COALESCE is equivalent to a CASE expression that returns the first NOT NULL expression in a list of expressions.

COALESCE(expression1, expression2, ... expressionN)

COALESCE() saves quite a lot of IF or CASE decision logic.

4>
5> CREATE TABLE #ProductPrices (
6> ProductName VarChar(25),
7> SuperSalePrice Money NULL,
8> SalePrice Money NULL,
9> ListPrice Money NULL)
10> GO
1>
2> SELECT ProductName, COALESCE(SuperSalePrice, SalePrice, ListPrice) AS CurrentPrice
3> FROM #ProductPrices
4> GO
ProductName               CurrentPrice
------------------------- ---------------------
(0 rows affected)
1>
2> drop table #ProductPrices;
3> GO

Returning the First Non NULL Value in a List of Expressions

5> DECLARE @Value1 int
6> DECLARE @Value2 int
7> DECLARE @Value3 int
8> SET @Value2 = 22
9> SET @Value3 = 955
10> SELECT COALESCE(@Value1, @Value2, @Value3)
11> GO
-----------
         22
(1 rows affected)
1>