SQL Server/T-SQL Tutorial/Data Convert Functions/COALESCE
Содержание
- 1 A SELECT statement that substitutes a different data type
- 2 A SELECT statement that uses the COALESCE function
- 3 COALESCE is equivalent to a CASE expression that returns the first NOT NULL expression in a list of expressions.
- 4 COALESCE() saves quite a lot of IF or CASE decision logic.
- 5 Returning the First Non NULL Value in a List of Expressions
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>