SQL Server/T-SQL Tutorial/Data Convert Functions/ISNULL
Содержание
- 1 Combine CAST and ISNULL
- 2 Performing Flexible Searches Using ISNULL
- 3 Replacing null values with zero values with ISNULL
- 4 SELECT statement using the ISNULL function
- 5 System function ISNULL allows a display of the specified value as substitution for NULL.
- 6 The syntax of the ISNULL function
- 7 Update and ISNULL
Combine CAST and ISNULL
<source lang="sql">
6> CREATE TABLE Customers ( 7> CustomerID nchar (5) NOT NULL , 8> CompanyName nvarchar (40) NOT NULL , 9> ContactName nvarchar (30) NULL , 10> ContactTitle nvarchar (30) NULL , 11> Address nvarchar (60) NULL , 12> City nvarchar (15) NULL , 13> Region nvarchar (15) NULL , 14> PostalCode nvarchar (10) NULL , 15> Country nvarchar (15) NULL , 16> Phone nvarchar (24) NULL , 17> Fax nvarchar (24) NULL 18> ) 19> GO 1> 2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111") 3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL) 4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444") 5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55") 6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777") 7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32") 8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99") 9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41") 10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745") 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> CREATE TABLE Orders ( 2> OrderID int NOT NULL , 3> CustomerID nchar (5) NULL , 4> EmployeeID int NULL , 5> OrderDate datetime NULL , 6> RequiredDate datetime NULL , 7> ShippedDate datetime NULL , 8> ShipVia int NULL , 9> Freight money NULL DEFAULT (0), 10> ShipName nvarchar (40) NULL , 11> ShipAddress nvarchar (60) NULL , 12> ShipCity nvarchar (15) NULL , 13> ShipRegion nvarchar (15) NULL , 14> ShipPostalCode nvarchar (10) NULL , 15> ShipCountry nvarchar (15) NULL 16> ) 17> GO 1> 2> 3> SELECT cu.rupanyName, 4> ISNULL(CAST ((SELECT MIN(o.OrderDate) 5> FROM Orders o 6> WHERE o.CustomerID = cu.CustomerID)AS varchar), "NEVER ORDERED") 7> AS "Order Date" 8> FROM Customers cu 9> GO CompanyName Order Date
------------------------------
A NEVER ORDERED M NEVER ORDERED H NEVER ORDERED B NEVER ORDERED S NEVER ORDERED F NEVER ORDERED C NEVER ORDERED P NEVER ORDERED D NEVER ORDERED (9 rows affected) 1> 2> drop table Orders; 3> drop table Customers; 4> GO</source>
Performing Flexible Searches Using ISNULL
<source lang="sql">
3> 4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20), 12> managerid INTEGER 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", NULL,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,NULL ,"North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat NULL 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 NULL North Toronto 10
(9 rows affected) 1> 2> 3> 4> DECLARE @EID int 5> DECLARE @StartDate datetime 6> DECLARE @Cost money 7> 8> SET @EID = 1 9> 10> SELECT ID, Start_Date, salary 11> FROM employee 12> WHERE ID = ISNULL(@EID, ID) AND 13> Start_Date = ISNULL(@StartDate, Start_Date) AND 14> Salary = ISNULL(@Cost, Salary) 15> GO ID Start_Date salary
----------------------- ------------
1 2005-03-22 00:00:00.000 5890.00
(1 rows affected) 1> 2> drop table employee; 3> GO</source>
Replacing null values with zero values with ISNULL
<source lang="sql">
4> 5> CREATE TABLE titles( 6> title_id varchar(20), 7> title varchar(80) NOT NULL, 8> type char(12) NOT NULL, 9> pub_id char(4) NULL, 10> price money NULL, 11> advance money NULL, 12> royalty int NULL, 13> ytd_sales int NULL, 14> notes varchar(200) NULL, 15> pubdate datetime NOT NULL 16> ) 17> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE sales( 4> stor_id char(4) NOT NULL, 5> ord_num varchar(20) NOT NULL, 6> ord_date datetime NOT NULL, 7> qty smallint NOT NULL, 8> payterms varchar(12) NOT NULL, 9> title_id varchar(80) 10> ) 11> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT ISNULL(qty,0) AS qty, title 3> FROM titles t 4> LEFT OUTER JOIN sales s ON t.title_id = s.title_id 5> GO qty title
--------------------------------------------------------------------------------
75 Secrets
10 Secrets
20 The
10 The
25 Emotional
15 Emotional
0 Prolonged
0 With
0 Valley
0 Any?
0 Fifty
(11 rows affected) 1> 2> 3> drop table titles; 4> drop table sales; 5> GO</source>
SELECT statement using the ISNULL function
<source lang="sql">
8> 9> create table Billings ( 10> BankerID INTEGER, 11> BillingNumber INTEGER, 12> BillingDate datetime, 13> BillingTotal INTEGER, 14> TermsID INTEGER, 15> BillingDueDate datetime , 16> PaymentTotal INTEGER, 17> CreditTotal INTEGER 18> 19> ); 20> 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> SELECT BillingDate, 2> ISNULL(BillingDate, "1900-01-01") AS NewDate 3> FROM Billings 4> 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> 2> drop table Billings; 3> GO</source>
System function ISNULL allows a display of the specified value as substitution for NULL.
<source lang="sql">
3> 4> 5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, NULL , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 NULL Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> SELECT ISNULL(first_name, "first name unknown") task 4> FROM employee 5> GO task
Jason Alison James Celia Robert Linda David James first name (9 rows affected) 1> 2> drop table employee; 3> GO</source>
The syntax of the ISNULL function
<source lang="sql">
ISNULL(check_expression, replacement_value)</source>
Update and ISNULL
<source lang="sql">
4> CREATE TABLE OrderDetails( 5> orderID int NOT NULL, 6> PartID int NOT NULL, 7> Quantity int NOT NULL); 8> GO 1> 2> 3> 4> INSERT INTO OrderDetails VALUES(10001, 11, 12) 5> INSERT INTO OrderDetails VALUES(10001, 42, 10) 6> INSERT INTO OrderDetails VALUES(10001, 72, 5) 7> INSERT INTO OrderDetails VALUES(10002, 14, 9) 8> INSERT INTO OrderDetails VALUES(10002, 51, 40) 9> INSERT INTO OrderDetails VALUES(10003, 41, 10) 10> INSERT INTO OrderDetails VALUES(10003, 61, 35) 11> INSERT INTO OrderDetails VALUES(10003, 65, 15) 12> 13> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE PROC dbo.usp_OrderDetailsUpdate 3> @orderid int, 4> @partid int, 5> @neworderid int = NULL, 6> @newpartid int = NULL, 7> @quantity int = NULL 8> AS 9> UPDATE OrderDetails 10> SET orderid = ISNULL(@neworderid, orderid), 11> partid = ISNULL(@newpartid, partid), 12> quantity = ISNULL(@quantity, quantity) 13> WHERE 14> orderid = @orderid 15> AND 16> partid = @partid 17> GO 1> 2> 3> 4> 5> drop table OrderDetails; 6> GO 1></source>
