November 22, 2014
Hot Topics:

Introductory Transact-SQL

  • May 7, 2003
  • By Addison Wesley
  • Send Email »
  • More Articles »

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.

CASE

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')

or

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

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.

Filtering Data

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




Page 5 of 10



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel