Introductory Transact-SQL, Page 5
Converting Data Types
Converting data between types is equally simple. You can use either the CAST() or CONVERT() function to convert one data type to another, but CAST() is the SQL-92 P compliant method. Here's a SELECT that converts the Amount column in the orders table to a character string:
SELECT CAST(Amount AS varchar) FROM orders--------123.45678.9086753.09678.90
Here's an example that illustrates how to convert a datetime value to a character string using a specific format:
SELECT CONVERT(char(8), GETDATE(),112)--------19690720
This example highlights one situation in which CONVERT() offers superior functionality to CAST(). CONVERT() supports a style parameter (the third argument above) that specifies the exact format to use when converting a datetime value to a character string. You can find the table of supported styles in the Books Online, but styles 102 and 112 are probably the most common.
In the examples throughout this book, you'll find copious use of the CASE function. CASE has two basic forms. In the simpler form, you specify result values for each member of a series of expressions that are compared to a determinant or key expression, like so:
SELECT CASE sexWHEN 0 THEN 'Unknown'WHEN 1 THEN 'Male'WHEN 2 THEN 'Female'ELSE 'Not applicable'END
In the more complex form, known as a "searched" CASE, you specify individual result values for multiple, possibly distinct, logical expressions, like this:
SELECT CASEWHEN DATEDIFF(dd,RentDueDate,GETDATE())>15 THEN DespositWHEN DATEDIFF(dd,RentDueDate,GETDATE())>5 THEN DailyPenalty*DATEDIFF(dd,RentDueDate,GETDATE())ELSE 0END
A searched CASE is similar to an embedded IF...ELSE, with each WHEN performing the function of a new ELSE clause.
Personally, I've never liked the CASE syntax. I like the idea of a CASE function, but I find the syntax unwieldy. It behaves like a function in that it can be nested within other expressions, but syntactically, it looks more like a flow-control statement. In some languages, "CASE" is a flow-control keyword that's analogous to the C/C11 switch statement. In Transact-SQL, CASE is used similarly to an inline or "immediate" IF—it returns a value based on if-then-else logic. Frankly, I think it would make a lot more sense for the syntax to read something like this:
CASE(sex, 0, 'Unknown', 1, 'Male', 2, 'Female', 'Unknown')
CASE(DATEDIFF(dd,RentDueDate,GETDATE())>15, Deposit,DATEDIFF(dd,RentDueDate,GETDATE())>5, DailyPenalty*DATEDIFF(dd,RentDueDate,GETDATE()),0)
This is the way that the Oracle DECODE() function works. It's more compact and much easier to look at than the cumbersome ANSI CASE syntax.
Aggregate columns consist of special functions that perform some calculation on a set of data. Examples of aggregates include the COUNT(), SUM(), AVG(), MIN(), STDDEV(), VAR(), and MAX() functions. They're best understood by example. Here's a command that returns the total number of customer records on file:
SELECT COUNT(*) FROM customers
Here's one that returns the dollar amount of the largest order on file:
SELECT MAX(Amount) FROM orders
And here's one that returns the total dollar amount of all orders:
SELECT SUM(Amount) FROM orders
Aggregate functions are often used in tandem with SELECT's GROUP BY clause (covered below) to produce grouped or partitioned aggregates. They can be employed in other uses as well (e.g., to "hide" normally invalid syntax), as the chapters on statistical computations illustrate.
You use the SQL WHERE clause to qualify the data a SELECT statement returns. It can also be used to limit the rows affected by an UPDATE or DELETE statement. Here are some queries that use WHERE to filter the data they return:
SELECT UPPER(LastName), FirstNameFROM customersWHERE State='TX' FirstName--- ---------DOE JohnDOE Jane
The following code restricts the customers returned to those whose address contains the word "Joshua."
SELECT LastName, FirstName, StreetAddress FROM customersWHERE StreetAddress LIKE '%Joshua%'LastName FirstName StreetAddress-------- --------- ---------------Doe John 123 Joshua TreeDoe Jane 123 Joshua Tree
Note the use of "%" as a wildcard. The SQL wildcard % (percent sign) matches zero or more instances of any character, while _ (underscore) matches exactly one.
Here's a query that returns the orders exceeding $500:
SELECT OrderNumber, OrderDate, AmountFROM ordersWHERE Amount > 500OrderNumber OrderDate Amount----------- ----------------------- --------102 1992-02-27 00:00:00.000 678.90103 1995-05-20 00:00:00.000 86753.09104 1997-11-21 00:00:00.000 678.90
The following example uses the BETWEEN operator to return orders occurring between October 1990 and May 1995, inclusively. I've included the time with the second of the two dates because, without it, the time would default to midnight (SQL Server datetime columns always store both the date and time; an omitted time defaults to midnight), making the query noninclusive. Without specification of the time portion, the query would return only orders placed up through the first millisecond of May 31.
SELECT OrderNumber, OrderDate, Amount FROM ordersWHERE OrderDate BETWEEN '10/01/90' AND '05/31/95 23:59:59.999'OrderNumber OrderDate Amount----------- ----------------------- --------101 1990-10-18 00:00:00.000 123.45102 1992-02-27 00:00:00.000 678.90103 1995-05-20 00:00:00.000 86753.09