Chapter 6: SQL Functions

Overview

Now that you understand how to formulate SQL queries and return result sets, you need to do something useful with this data. Once you have successfully retrieved values from tables, it's very common to further manipulate values to provide useful and meaningful results. This may involve the following:

• Performing calculations and mathematical operations

• Conversion

• Parsing

• Combining values

• Aggregation

The purpose of this chapter is to help you learn the mechanics of using functions of all kinds. It introduces you to some of the more common value manipulation functions and some less-common functions to give a sample of these powerful capabilities. You'll also take a look at some new functionality offered in SQL Server 2005.

At the end of the book, you'll find a reference for all of the system-supplied functions and the syntax needed to use them. Additionally, subsequent chapters contain more detailed information about specific groups of functions. For example, Chapter 7 discusses specific uses for aggregate functions in more advanced SQL queries, and Chapter 11 shows you how to use functions to support full-text index searches.

Transact-SQL functions are grouped into the categories described in the following table.

Open table as spreadsheet

Function Category	Purpose
Aggregation	Aggregate functions return a scalar value representing an aggregation over a range of values, applying a specific aggregate selection or summary.
Configuration Variables	Return information about the SQL Server execution environment that may be useful in programming objects.
Conversion	Used to convert values of one data type to another. Also used to apply formatting characteristics to dates, times, and numeric values.
Cursor	Used to loop through the rows in a result set in a procedural manner when iterating through a cursor.
Date	A set of functions used for parsing the date and time portions of a date value, and for comparing and manipulating date/time values.
Image/Text	Includes both specialized functionality, specific to the Text, nText, and Image types; and functions equivalent to those for standard character types.
Mathematical	Used to perform a variety of common and specialized mathematical operations. Useful in performing algebraic, trigonometric, statistical, approximating, and financial operations.
Metadata	Utility functions that return information about the SQL Server configuration details and details about the server and database settings.
Ranking	New function in SQL Server 2005 used to enumerate sorted and top-valued result sets.
Security	Returns role membership and privilege information for SQL Server users. Also includes a set of functions to manage events and traces.
String Manipulation	Used to parse, replace, and manipulate character values.
System	Utility functions used to perform a variety of tasks. These include value comparisons and value type testing. This category is also a catch-all for other functionality.
System Statistical	Administrative utilities used to discover database system usage and environment information.