Combine CAST and ISNULL : ISNULL « Data Convert Functions « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Data Convert Functions
3. 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.CompanyName,
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