How to Retrieve Data from a Single Table - How to Use Functions
(Page 7 of 17 )
Figure 7 introduces you to functions and illustrates how you use them in column specifications. A function performs an operation and returns a value. For now, don't worry about the details of how the functions shown here work. You'll learn more about all of these functions in chapter 8. Instead, just focus on how they're used in column specifications.
To code a function, you begin by entering its name followed by a set of parentheses. If the function requires one or more parameters, you enter them within the parentheses and separate them with commas. When you enter a parameter, you need to be sure it has the correct data type. You'll learn more about that in chapter 8.
The first example in this figure shows how to use the LEFT function to extract the first character of the VendorContactFName and VendorContactLName columns. The first parameter of this function specifies the string value, and the second parameter specifies the number of characters to return. The results of the two functions are then concatenated to form initials as shown in the result set for this statement.
The second example shows how to use the CONVERT function to change the data type of a value. This function requires two parameters. The first parameter specifies the new data type, and the second parameter specifies the value to convert. In addition, this function accepts an optional third parameter that specifies the format of the returned value. The first CONVERT function shown here, for example, converts the PaymentDate column to a character value with the format mm/dd/yy. And the second CONVERT function converts the PaymentTotal column to a variable-length character value that's formatted with commas. These functions are included in a string expression that concatenates their return values with the InvoiceNumber column and three literal values.
The third example uses two functions that work with dates. The first one, GETDATE, returns the current date. Notice that although this function doesn't accept any parameters, the parentheses are still included. The second function, DATEDIFF, gets the difference between two date values. This function requires three parameters. The first one specifies the units in which the result will be expressed. In this example, the function will return the number of days between the two dates. The second and third parameters specify the start date and the end date. Here, the second parameter is the invoice date and the third parameter is the current date, which is obtained using the GETDATE function.
Figure 7: A SELECT statement that uses the LEFT function
SELECT VendorContactFName, VendorContactLName,
LEFT(VendorContactFName, 1) +
LEFT(VendorContactLName, 1) AS Initials
FROM Vendors
A SELECT statement that uses the CONVERT function:
SELECT 'Invoice: #' + InvoiceNumber
+ ', dated ' + CONVERT(char(8), PaymentDate, 1)
+ ' for $' + CONVERT(varchar(9), PaymentTotal, 1)
FROM Invoices
A SELECT statement that computes the age of an invoice:
SELECT InvoiceDate,
GETDATE() AS 'Today''s Date',
DATEDIFF(day, InvoiceDate, GETDATE()) AS Age
FROM Invoices
Description
- An expression can include any of the functions that are supported by SQL Server. A function performs an operation and returns a value.
- A function consists of the function name, followed by a set of parentheses that contains any parameters, or arguments, required by the function. If a function requires two or more arguments, you separate them with commas.
- For more information on using functions, see chapter 8.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Eliminate Duplicate Rows >>
More MS SQL Server Articles
More By Bryan Syverson
