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
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
Performing Flexible Searches Using ISNULL
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
Replacing null values with zero values with ISNULL
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
SELECT statement using the ISNULL function
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
System function ISNULL allows a display of the specified value as substitution for NULL.
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
The syntax of the ISNULL function
ISNULL(check_expression, replacement_value)
Update and ISNULL
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>