Microsoft SQL Server DBA Survival Guide --Appendix D Appendix D Function Reference Aggregate Functions Date Functions Mathematical Functions Niladic Functions String Functions System Functions Text/Image Functions Type-Conversion Function SQL Server contains several built-in functions that can be used with the Transact SQL language. This appendix categorizes the built-in functions. Aggregate Functions The following sections cover aggregate functions. Parameter Explanation The ALL parameter applies the aggregate function to all values. ALL is the default. The DISTINCT parameter applies the aggregate function to only distinct values. The expression parameter is a column name. AVG([ALL | DISTINCT] expression) Sum of values in a column.NULLs are ignored. COUNT([ALL | DISTINCT] expression) Count number of non-NULL values in a column. NULLs are ignored. COUNT(*) Count number of rows. NULLs are counted. MAX(expression) Maximum value for a column. NULLs are ignored. MIN(expression) Minimum value for a column. NULLs are ignored. SUM([ALL | DISTINCT] expression) Sum of values for a column. NULLs are ignored. Date Functions The following sections cover date functions. Parameter Explanation The date parameter is a valid date. The datepart parameter is a date part or abbreviation. (See Table D.1 for valid date part abbreviations.) The number parameter is a valid number. Table D.1. Valid date part abbreviations. Date Part Abbreviation Values year yy 1753-9999 quarter qq 1-4 month mm 1-12 day of year dy 1-366 day dd 1-31 week wk 1-53 weekday dw 1-7 (Sun.-Sat.) hour hh 0-23 minute mi 0-59 second ss 0-59 millisecond ms 0-999 DATEADD(datepart,number,date) Returns a date incremented by the specified value. DATEDIFF(datepart,number,date) Returns the date part difference between a number and a date. DATENAME(datepart,date) Returns the date part of a specified date as a string. DATEPART(datepart,date) Returns the date part of a specified date as an integer. GETDATE() Returns the current date and time. ISDATE(test_expr) Returns 1 if test_expr is a valid date; returns 0 if the expression is not a valid date. Mathematical Functions The following sections cover mathematical functions. ABS(numeric_expr) Returns the absolute value of a specified expression. ACOS(float_expr) Returns the angle in radians of a cosine expression. ASIN(float_expr) Returns the angle in radians of a sine expression. ATAN(float_expr) Returns the angle in radians of a tangent expression. ATN2(float_expr1,float_expr2) Returns the angle in radians of a tangent expression. CEILING(numeric_expr) Returns a rounded-up integer based on the specified expression. COS(float_expr) Returns the cosine of a specified expression. COT(float_expr) Returns the cotangent of a specified expression. DEGREES(numeric_expr) Returns the degrees of a specified expression. EXP(float_expr) Returns the exponential value of a specified expression. FLOOR(numeric_expr) Returns a rounded-down integer based on the specified expression. ISNUMERIC(numeric_expr) Returns 1 if numeric_expr is a valid numeric expression; returns 0 if the expression is not a valid numeric expression. LOG(float_expr) Returns the natural logarithm of a specified expression. LOG10(float_expr) Returns the base-10 logarithm of a specified expression. PI() Returns pi. POWER(numeric_expr,y) Returns the value of numeric_expr to the power of y. RADIANS(numeric_expr) Returns the radians of a specified expression. RAND([integer_expr]) Returns a random float number between 0 and 1. Use the optional integer_expr as the seed value. ROUND(numeric_expr,integer_expr) Returns a number rounded to the precision specified by integer_expr. SIGN(numeric_expr) Returns +1, 0, or -1 based on the sign of the expression. SIN(float_expr) Returns the sine of an angle specified in radians. SQRT(float_expr) Returns the square root of a specified expression. TAN(float_expr) Returns the tangent of an angle specified in radians. Niladic Functions Niladic functions are new to SQL Server 6.x. These functions allow default values to be inserted into a table. Before version 6.x, you had to use triggers to insert these types of default values. For more information about these functions, see the CREATE TABLE statement in SQL Server's Books Online. CURRENT_TIMESTAMP Returns the current date and time. CURRENT_USER Returns the name of the person doing the insert. SESSION_USER Returns the name of the person doing the insert. SYSTEM_USER Returns the login ID of the person doing the insert (same as SESSION_USER). USER Returns the name of the person doing the insert (same as SESSION_USER and SYSTEM_USER). String Functions The following sections cover string functions. + Concatenates two or more nonnumeric expressions. ASCII(char_expr) Returns the corresponding ASCII code value of a specified expression. CHAR(integer_expr) Returns the corresponding character from the specified ASCII code value. The code must be between 0 and 255. CHARINDEX(pattern, expression) Returns the first position of a pattern within an expression. DIFFERENCE(char_expr1, char_expr2) Determines the similarities between two strings and returns a value rating the similarities on a scale of 0 to 4, with 4 being the best match. LOWER(char_expr) Converts an expression to lowercase. LTRIM(char_expr) Removes leading spaces. PATINDEX(%pattern%, expression) Returns the first position of a pattern in the specified expression. REPLICATE(char_expr, integer_expr) Replicates a character expression integer_expr number of times. REVERSE(char_expr) Returns a reversed expression. RIGHT(char_expr, integer_expr) Returns integer_expr number of characters from a character expression starting at the right side of char_expr. RTRIM(char_expr) Removes trailing spaces. SOUNDEX(char_expr) Returns a four-digit SOUNDEX code. SPACE(integer_expr) Returns integer_expr number of spaces. STR(float_expr [, length [, decimal]]) Returns a character string converted from numeric data. STUFF(char_expr1, start, length, char_expr2) Stuffs char_expr1 into char_expr2. SUBSTRING(expression, start, length) Returns a portion of a string expression defined by the start value and the length value. UPPER(char_expr) Converts an expression to uppercase. System Functions The following sections cover system functions. APP_NAME() Returns the application name for the current connection. COALESCE(expression1, expression2, ... expressionN) Returns the first non-NULL expression in a list of expressions. COL_LENGTH(table_name, column_name) Returns the length of a column in a table. COL_NAME(table_id, column_id) Returns the name of a column based on ID. DATALENGTH(expression) Returns the length of a specified expression. DB_ID([database_name]) Returns the database identification number of a specified database name. DB_NAME([database_id]) Returns the database name for a specified database ID. GETANSINULL([database_name]) Returns the nullability setting for a database. HOST_ID() Returns the workstation identification number. HOST_NAME() Returns the workstation name. IDENT_INCR(table_name) Returns the increment value used for the creation of an identity column. IDENT_SEED(table_name) Returns the seed value used for the creation of an identity column. INDEX_COL(table_name, index_id, key_id) Returns the index name for an indexed column. ISNULL(expression, value) Replaces a NULL expression with a specified value. NULLIF(expression1, expression2) Returns NULL when expression1 equals expression2. OBJECT_ID(object_name) Returns the ID for a specified object name. OBJECT_NAME(object_id) Returns the name for a specified object ID. STATS_DATE(table_id, index_id) Returns a date indicating when an index's statistics were last updated. SUSER_ID([login_name]) Returns the login ID for a specified login name. SUSER_NAME([server_user_id]) Returns the login name for a specified login ID. USER([user_id]) Returns the user's database name for a specified user ID (same as USER_NAME()). USER_ID([user_name]) Returns the user's database ID for a specified user name. USER_NAME([user_id]) Returns the user's database name for a specified user ID. Text/Image Functions The following sections cover text and image functions. DATALENGTH(expression) Returns the length of a specified expression. PATINDEX(%pattern%, expression) Returns the first position of a pattern in the specified expression. TEXTPTR(column_name) Returns the text-pointer value. TEXTVALID(table_name.column_name, text_ptr) Returns 1 if the text pointer is valid and 0 if the pointer is invalid. Type-Conversion Function The following section covers the CONVERT function. CONVERT(datatype[(length)], expression [, style]) Parameter Explanation The datatype parameter is any valid SQL Server datatype. The length parameter is used with char, varchar, binary, and varbinary datatypes. The expression parameter is the value to convert. The style parameter is the date format to use with datetime or smalldatetime data conversion. (See Table D.2 for valid styles.) Table D.2. Valid styles. Without Century(yy) With Century (yyyy) Standard Output -- 0 or 100 Default mon dd yyyy hh:miAM (or PM) 1 101 USA mm/dd/yy 2 102 ANSI yy.mm.dd 3 103 British/French dd/mm/yy 4 104 German dd.mm.yy 5 105 Italian dd-mm-yy 6 106 -- dd mon yy 7 107 -- mon dd, yy 8 108 -- hh:mm:ss -- 9 or 109 Default + milliseconds mon dd yyyy (hh:mi:ss:mmmAM (or PM)) 10 110 USA mm-dd-yy 11 111 JAPAN yy/mm/dd 12 112 ISO yymmdd -- 13 or 113 European default + dd mon yyyy millisecondshh:mi:ss:mmm(24h) 14 114 -- hh:mi:ss:mmm(24h) DISCLAIMER To order books from QUE, call us at 800-716-0044 or 317-361-5400. For comments or technical support for our books and software, select Talk to Us. © 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.