Functions
Functions can be
used to modify a column value in transit. Transact-SQL provides a bevy of
functions that
can be roughly
divided into six major groups: string functions, numeric functions, date
functions,
aggregate function,
system functions, and meta-data functions. Here's a Transact-SQL function in
action:
SELECT
UPPER(LastName), FirstName
FROM customers
FirstName
--------------
---------
DOE John
DOE Jane
CITIZEN John
Guru’s Guide to
Transact-SQL
8
Here, the UPPER()
function is used to uppercase the LastName column as it's returned in the
result set.
This affects only
the result set—the underlying data is unchanged.
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–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.45
678.90
86753.09
678.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 species 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
styles102 and 112 are probably the most common.
CASE
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 sex
WHEN 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 CASE
WHEN
DATEDIFF(dd,RentDueDate,GETDATE())>15 THEN Desposit
WHEN
DATEDIFF(dd,RentDueDate,GETDATE())>5 THEN DailyPenalty*
DATEDIFF(dd,RentDueDate,GETDATE())
ELSE 0
END
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/C++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.
No comments:
Post a Comment