SQL Server 2008 function types in T-SQL
Transact-SQL Functions
Transact-SQL functions can be either aggregate functions or scalar functions. The following
sections describe these function types.
Aggregate functions
Aggregate functions are applied to a group of data values from a column. Aggregate
functions always return a single value. Transact-SQL supports several groups of aggregate
functions:
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in October 2008
-
 |
| |
| You are reading part 3 from "The basic
objects of T-SQL in SQL Server 2008," excerpted from Microsoft SQL Server 2008: A Beginner's
Guide, by Dusan Petkovic, copyright 2008, printed with permission from McGraw-Hill Osborne
Media. |
|
|
|
|
- Convenient aggregate functions
- Statistical aggregate functions
- User-defined aggregate functions
- Analytic aggregate functions
Statistical and analytic aggregates are discussed in Chapter 24. User-defined aggregates are
beyond the scope of this book. That leaves the convenient aggregate functions, described next:
- AVG - Calculates the arithmetic mean (average) of the data values contained within a
column. The column must contain numeric values.
- MAX and MIN - Calculate the maximum and minimum data value of the column, respectively.
The column can contain numeric, string, and date/time values.
- SUM - Calculates the total of all data values in a column. The column must contain
numeric values.
- COUNT - Calculates the number of (non-null) data values in a column. The only aggregate
function not being applied to columns is COUNT(*). This function returns the number of rows
(whether or not particular columns have NULL values).
- COUNT_BIG - Analogous to COUNT, the only difference being that COUNT_BIG returns a value
of the BIGINT data type.
The use of convenient aggregate functions with the SELECT statement are described in detail in
Chapter 6.
Scalar functions
In addition to aggregate functions, Transact-SQL provides several scalar functions that are used
in the construction of scalar expressions. (A scalar function operates on a single value or list of
values, as opposed to aggregate functions, which operate on the data from multiple rows.) Scalar
functions can be categorized as follows:
- Numeric functions
- Date functions
- String functions
- System functions
- Metadata functions
The following sections describe these function types.
Numeric functions
Numeric functions within Transact-SQL are mathematical functions for modifying numeric values.
The following numeric functions are available:
| Function |
Explanation |
| ABS(n) |
Returns the absolute value (i.e., negative values are returned as positive) of the numeric
expression n. Example:
SELECT ABS(–5.767) = 5.767, SELECT ABS(6.384) = 6.384 |
| ACOS(n) |
Calculates arc cosine of n. n and the resulting value belong to the FLOAT data
type. |
| ASIN(n) |
Calculates the arc sine of n. n and the resulting value belong to the FLOAT data
type. |
| ATAN(n) |
Calculates the arc tangent of n. n and the resulting value belong to the FLOAT
data type. |
| ATN2(n,m) |
Calculates the arc tangent of n/m. n, m, and the resulting value belong to the FLOAT
data type. |
| CEILING(n) |
Returns the smallest integer value greater or equal to the specified parameter. Examples:
SELECT CEILING(4.88) = 5
SELECT CEILING(–4.88) = –4 |
| COS(n) |
Calculates the cosine of n. n and the resulting value belong to the FLOAT data
type. |
| COT(n) |
Calculates the cotangent of n. n and the resulting value belong to the FLOAT data
type. |
| DEGREES(n) |
Converts radians to degrees. Examples:
SELECT DEGREES(PI()/2) = 90.0
SELECT DEGREES(0.75) = 42.97 |
| EXP(n) |
Calculates the value e^n. Example: SELECT EXP(1) = 2.7183 |
| FLOOR(n) |
Calculates the largest integer value less than or equal to the specified value n.
Example:
SELECT FLOOR(4.88) = 4 |
| LOG(n) |
Calculates the natural (i.e., base e) logarithm of n. Examples:
SELECT LOG(4.67) = 1.54
SELECT LOG(0.12) = –2.12 |
| LOG10(n) |
Calculates the logarithm (base 10) for n. Examples:
SELECT LOG10(4.67) = 0.67
SELECT LOG10(0.12) = –0.92 |
| PI() |
Returns the value of the number pi (3.14). |
| POWER(x,y) |
Calculates the value x^y. Examples: SELECT POWER(3.12,5) = 295.65
SELECT POWER(81,0.5) = 9 |
| RADIANS(n) |
Converts degrees to radians. Examples:
SELECT RADIANS(90.0) = 1.57
SELECT RADIANS(42.97) = 0.75 |
| RAND |
Returns a random number between 0 and 1 with a FLOAT data type. |
| ROUND(n, p,[t]) |
Rounds the value of the number n by using the precision p. Use positive values of
p to round on the right side of the decimal point and use negative values to round on the
left side. An optional parameter t causes n to be truncated. Examples:
SELECT ROUND(5.4567,3) = 5.4570
SELECT ROUND(345.4567,–1) = 350.0000
SELECT ROUND(345.4567,–1,1) = 340.0000 |
| ROWCOUNT_BIG |
Returns the number of rows that have been affected by the last Transact-SQL statement executed
by the system. The return value of this function has the BIGINT data type. |
| SIGN(n) |
Returns the sign of the value n as a number (+1 for positive, –1 for negative, and 0 for
zero).
Example:
SELECT SIGN(0.88) = 1 |
| SIN(n) |
Calculates the sine of n. n and the resulting value belong to the FLOAT data type. |
| SQRT(n) |
Calculates the square root of n. Example:
SELECT SQRT(9) = 3 |
| SQUARE(n) |
Returns the square of the given expression. Example:
SELECT SQUARE(9) = 81 |
| TAN(n) |
Calculates the tangent of n. n and the resulting value belong to the FLOAT data
type. |
Date Functions
Date functions calculate the respective date or time portion of an expression or return the
value from a time interval. Transact-SQL supports the following date functions:
| Function |
Explanation |
| GETDATE() |
Returns the current system date and time. Example:
SELECT GETDATE() = 2008-01-01 13:03:31.390 |
| DATEPART(item,date) |
Returns the specified part item of a date date as an integer. Examples:
SELECT DATEPART(month, '01.01.2005') = 1 (1 = January)
SELECT DATEPART(weekday, '01.01.2005') = 7 (7 = Sunday) |
| DATENAME(item,date) |
Returns the specified part item of the date date as a character string.
Example:
SELECT DATENAME(weekday, '01.01.2005') = Saturday |
| DATEDIFF(item,dat1,dat2) |
Calculates the difference between the two date parts dat1 and dat2 and returns
the result as an integer in units specified by the value item. Example:
SELECT DATEDIFF(year, BirthDate, GETDATE()) AS age FROM employee; -> returns the age of each
employee. |
| DATEADD(i,n,d) |
Adds the number n of units specified by the value i to the given date d.
Example:
SELECT DATEADD(DAY,3,HireDate) AS age FROM employee; -> adds three days to the starting date of
employment of every employee (see the sample database). |
String Functions
String functions are used to manipulate data values in a column, usually of a character data
type. Transact-SQL supports the following string functions:
| Function |
Explanation |
| ASCII(character) |
Converts the specified character to the equivalent integer (ASCII) code. Returns an integer.
Example:
SELECT ASCII('A') = 65 |
| CHAR(integer) |
Converts the ASCII code to the equivalent character. Example:
SELECT CHAR(65) = 'A'. |
| CHARINDEX(z1,z2) |
Returns the starting position where the partial string z1 first occurs in the string
z2. Returns 0 if z1 does not occur in z2. Example:
SELECT CHARINDEX('bl', 'table') = 3. |
| DIFFERENCE(z1,z2) |
Returns an integer, 0 through 4, that is the difference of SOUNDEX values of two strings
z1 and z2. (SOUNDEX returns a number that specifies the sound of a string. With this
method, strings with similar sounds can be determined.) Example:
SELECT DIFFERENCE('spelling', 'telling') = 2 (sounds a little bit similar, 0 = doesn't sound
similar) |
| LEFT(z, length) |
Returns the first length characters from the string z. |
| LEN(z) |
Returns the number of characters, instead of the number of bytes, of the specified string
expression, excluding trailing blanks. |
| LOWER(z1) |
Converts all uppercase letters of the string z1 to lowercase letters. Lowercase letters
and numbers, and other characters, do not change. Example:
SELECT LOWER('BiG') = 'big' |
| LTRIM(z) |
Removes leading blanks in the string z. Example:
SELECT LTRIM(' String') = 'String' |
| NCHAR(i) |
Returns the Unicode character with the specified integer code, as defined by the Unicode
standard. |
| QUOTENAME(char_string) |
Returns a Unicode string with the delimiters added to make the input string a valid delimited
identifier. |
| PATINDEX(%p%,expr) |
Returns the starting position of the first occurrence of a pattern p in a specified
expression expr, or zeros if the pattern is not found. Examples:
1) SELECT PATINDEX('%gs%', 'longstring') = 4;
2) SELECT RIGHT(ContactName, LEN(ContactName)-PATINDEX('% %',ContactName)) AS First_name FROM
Customers;
(The second query returns all first names from the customers column.) |
| REPLACE(str1,str2,str3) |
Replaces all occurrences of the str2 in the str1 with the str3.
Example:
SELECT REPLACE('shave' , 's' , 'be') = behave |
| REPLICATE(z,i) |
Repeats string z i times. Example:
SELECT REPLICATE('a',10) = 'aaaaaaaaaa' |
| REVERSE(z) |
Displays the string z in the reverse order. Example:
SELECT REVERSE('calculate') = 'etaluclac' |
| RIGHT(z,length) |
Returns the last length characters from the string z. Example:
SELECT RIGHT('Notebook',4) = 'book' |
| RTRIM(z) |
Removes trailing blanks of the string z. Example:
SELECT RTRIM('Notebook ') = 'Notebook' |
| SOUNDEX(a) |
Returns a four-character SOUNDEX code to determine the similarity between two strings.
Example:
SELECT SOUNDEX('spelling') = S145 |
| SPACE(length) |
Returns a string with spaces of length specified by length. Example:
SELECT SPACE = ' ' |
| STR(f,[len [,d]]) |
Converts the specified float expression f into a string. len is the length of the
string including decimal point, sign, digits, and spaces (10 by default), and d is the
number of digits to the right of the decimal point to be returned. Example:
SELECT STR(3.45678,4,2) = '3.46' |
| STUFF(z1,a,length,z2) |
Replaces the partial string z1 with the partial string z2 starting at position a,
replacing length characters of z1. Examples:
SELECT STUFF('Notebook',5,0, ' in a ') = 'Note in a book'
SELECT STUFF('Notebook',1,4, 'Hand') = 'Handbook' |
| SUBSTRING(z,a,length) |
Creates a partial string from string z starting at the position a with a length of
length.
Example:
SELECT SUBSTRING('wardrobe',1,4) = 'ward' |
| UNICODE |
Returns the integer value, as defined by the Unicode standard, for the first character of the
input expression. |
| UPPER(z) |
Converts all lowercase letters of string z to uppercase letters. Uppercase letters and
numbers do not change. Example:
SELECT UPPER('loWer') = 'LOWER' |
System Functions
System functions of Transact-SQL provide extensive information about database objects. Most
system functions use an internal numeric identifier (ID), which is assigned to each database object
by the system at its creation. Using this identifier, the system can uniquely identify each
database object. System functions provide information about the database system. The following
table describes several system functions. (For the complete list of all system functions, please
see Books Online.)
| Function |
Explanation |
| CAST(a AS type [(length)] |
Converts an expression a into the specified data type type (if possible).
a could be any valid expression. Example:
SELECT CAST(3000000000 AS BIGINT) = 3000000000 |
| COALESCE(a1,a2,…) |
Returns for a given list of expressions a1, a2,... the value of the first expression
that is not NULL. |
| COL_LENGTH(obj,col) |
Returns the length of the column col belonging to the database object (table or view)
obj. Example:
SELECT COL_LENGTH('customers', 'cust_ID') = 10 |
| CONVERT(type[(length)],a) |
Equivalent to CAST, but the arguments are specified differently. CONVERT can be used with any
data type. |
| CURRENT_TIMESTAMP |
Returns the current date and time. Example:
SELECT CURRENT_TIMESTAMP = '2008-01-01 17:22:55.670' |
| CURRENT_USER |
Returns the name of the current user. |
| DATALENGTH(z) |
Calculates the length (in bytes) of the result of the expression z. Example:
SELECT DATALENGTH(ProductName) FROM products. (This query returns the length of each field.) |
| GETANSINULL('dbname') |
Returns 1 if the use of NULL values in the database dbname complies with the ANSI SQL
standard. (See also the explanation of NULL values at the end of this chapter.) Example:
SELECT GETANSINULL('AdventureWorks') = 1 |
| ISNULL(expr, value) |
Returns the value of expr if that value is not null; otherwise, it returns value
(see Example 5.22). |
| ISNUMERIC(expression) |
Determines whether an expression is a valid numeric type. |
| NEWID() |
Creates a unique ID number that consists of a 16-byte binary string intended to store values of
the UNIQUEIDENTIFIER data type. |
| NEWSEQUENTIALID() |
Creates a GUID that is greater than any GUID previously generated by this function on a
specified computer. (This function can only be used as a default value for a column.) |
| NULLIF(expr1,expr2) |
Returns the NULL value if the expressions expr1 and expr2 are equal.
Example:
SELECT NULLIF(project_no, 'p1') FROM projects. (The query returns NULL for the project with the
project_no = 'p1'). |
| SERVERPROPERTY(propertyname) |
Returns the property information about the database server. |
| SYSTEM_USER |
Returns the login ID of the current user. Example:
SELECT SYSTEM_USER = LTB13942dusan |
| USER_ID([user_name]) |
Returns the identifier of the user user_name. If no name is specified, the identifier of
the current user is retrieved. Example:
SELECT USER_ID('guest') = 2 |
| USER_NAME([id]) |
Returns the name of the user with the identifier id. If no name is specified, the name
of the current user is retrieved. Example:
SELECT USER_NAME = 'guest' |
All string functions can be nested in any order; for example, REVERSE(CURRENT_USER).
Metadata Functions
Generally, metadata functions return information concerning the specified database and database
objects. The following table describes several metadata functions. (For the complete list of all
metadata functions, please see Books Online.)
| Function |
Explanation |
| COL_NAME(tab_id, col_id) |
Returns the name of a column belonging to the table with the ID tab_id and column ID
col_id. Example:
SELECT COL_NAME(OBJECT_ID('employee') , 3) = 'emp_lname' |
| COLUMNPROPERTY(id, col, property) |
Returns the information about the specified column. Example:
SELECT COLUMNPROPERTY(object_id('project'), 'project_no', 'PRECISION') = 4 |
| DATABASEPROPERTY(database, property) |
Returns the named database property value for the specified database and property.
Example:
SELECT DATABASEPROPERTY('sample', 'IsNullConcat') = 0. (The IsNullConcat property
corresponds to the option CONCAT_NULL_YIELDS_NULL, which is described at the end of this
chapter.) |
| DB_ID([db_name]) |
Returns the identifier of the database db_name. If no name is specified, the identifier
of the current database is returned. Example:
SELECT DB_ID('AdventureWorks') = 6 |
| DB_NAME([db_id]) |
Returns the name of the database with the identifier db_id. If no identifier is
specified, the name of the current database is displayed. Example:
SELECT DB_NAME(6) = 'AdventureWorks' |
| INDEX_COL(table, i, no) |
Returns the name of the indexed column in the table table, defined by the index
identifier i and the position no of the column in the index. |
| INDEXPROPERTY(obj_id, index_name, property) |
Returns the named index or statistics property value of a specified table identification
number, index or statistics name, and property name. |
| OBJECT_NAME(obj_id) |
Returns the name of the database object with the identifier obj_id. Example:
SELECT OBJECT_NAME(453576654) = 'products' |
| OBJECT_ID(obj_name) |
Returns the identifier of the database object obj_name. Example:
SELECT OBJECT_ID('products') = 453576654 |
| OBJECTPROPERTY(obj_id,property) |
Returns the information about the objects from the current database. |
Part 1:
Join the conversationComment
Share
Comments
Results
Contribute to the conversation