List Functions in T-SQL

The T-SQL database language includes many mathematical functions.

Microsoft's SQL Server and Sybase's software both use T-SQL as the language for maintaining and using databases. T-SQL is a dialect of SQL, the Structured Query Language developed in the 1970s. In addition to other features, T-SQL has a list of built-in functions for math, statistics, string manipulation and other common programming tasks.

String

String database fields contain character data such as names, state abbreviations and descriptions. T-SQL has several string functions to combine, split, fill and trim character fields; for example, removing trailing spaces from a field requires the rtrim() function. The ltrim() function removes spaces from the beginning of a field, up to the first nonblank character. The left() and right() functions give you the leftmost or rightmost "n" characters in the field, where "n" is an integer.

To make a list of the first 10 characters of names in a customer table, you might use the following T-SQL expression:

SELECT left(name,10) FROM customers;

Other string functions include upper() and lower() to change the case of a field to all uppercase or lowercase, respectively; stuff(), which inserts characters into a field; and len(), which returns the length of the character string as an integer.

Aggregate

Aggregate functions operate on fields for all records in a table or table subset, returning a single value. The count() function, for example, gives you the number of records in a table. Others, such as min(), max() and avg(), compute statistics. Sum() works on numeric fields, giving you a total. You can use all of these functions on a single field. A query to find the minimum, maximum, average and total sales from a sales table looks like this in T-SQL code:

SELECT min(sales-amount), max(sales-amount), avg(sales-amount), sum(sales-amount) FROM sales-table;

Math

T-SQL includes mathematical functions useful for scientific or engineering work. Trigonometric functions include sin(), cos() and tan(), along with their inverse functions, asin(), acos() and atan(). As with programming languages such as FORTRAN, T-SQL trig functions require angle measurements in terms of radians, not degrees; the radians() function is a handy conversion that returns radians if your field values are in degrees. So, a scientist making list of sine calculations from a table of degree measurements uses this statement:

SELECT sin(radians(angle)) FROM measurements;

User-Defined Functions

The T-SQL language lets you define your own functions with the CREATE FUNCTION statement. The database stores user-defined functions permanently, allowing you to build an extensive custom library of functions. Once created, you can use a function in any subsequent T-SQL statement. If you need to update a function with a new formula or data, you use the ALTER FUNCTION statement. You can remove obsolete user-defined functions from the database with the DROP FUNCTION statement.

References

• Oracle: History of SQL
• Microsoft: Built-in Functions (Transact-SQL)