TSQL Tutorial:Function ,Case , Data Type Conversion

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.