SQL Server Functions

With version 7.0, Microsoft added more than 30 functions to an already large number of functions. More were added in SQL Server 2000 as well to support new features or capabilities, or to provide additional functionality.

Some of the SQL Server functions provide shortcuts to obtain information that could be retrieved in other ways. For instance, one function will obtain an object ID (OBJECT_ID()) if you know the object's name, but looking it up in the sysobjects table could also work.

Other functions, such as some of the mathematical functions, are more essential. (Okay, it could be argued that you can calculate the square root, for instance, using T-SQL code, but it is not efficient.)

Most functions have the following structure:

FUNCTION_NAME([parameter1 [, parameter2 [, ...]]) 

The parameters might be an expression (such as a column name or another function), a constant, or a special code (such as a formatting code).

A function returns a value. The datatype for the value depends on the function you are using. Take a look at the available functions grouped by category.

String Functions

The string functions allow you to perform concatenation, parsing manipulation, and so on with strings.


Excessive use of string functions against a column might indicate that the column should be split into several columns. For example, if you find yourself frequently parsing out first name and last name from a name column, perhaps you should split the name into two columns.

Table 26.3 lists the available string functions. They can be used against any string expression.

Table 26.3. String Functions
Function Name Returns
ASCII(char) The ASCII code for the leftmost character in char.
CHAR(int) The ASCII character represented by int (an ASCII code).
CHARINDEX(char_pattern, char, [int_start]) Starting location of char_pattern within char, optionally starting search at int_start.
DIFFERENCE(char1, char2) The difference between the two character expressions. Used for a phonetic match.
LEFT(char, int) int characters from left of char.
LEN(char) Number of characters in char, excluding trailing blanks.
LOWER(char) char in lowercase.
LTRIM(char) char without leading spaces.
NCHAR(int) The character for a given Unicode value.
PATINDEX(char_pattern, char) Starting position of char_pattern in char, or 0 if the pattern is not found.
REPLACE(char1, char2, char3) String with all occurrences of char2 replaced with char3 in char1.
QUOTENAME(char, [char_quote]) char as a valid quoted identifier. Adds the characters [ and ] at the beginning and end of char (this default can be changed to ' or ", specified as char_quote). Returns a Unicode string.
REPLICATE(char, int) Repeat of char expression int number of times.
REVERSE(char) Reverse of char.
RIGHT(char, int) int characters from right of char.
RTRIM(char) char without trailing spaces.
SOUNDEX(char) A four-character string used for comparison of a phonetic match.
SPACE(int) A string of int spaces.
STR(float, [length, [decimal]]) float as a character string, with length of length and decimal numbers of decimals. Default length is 10 and default number of decimals is 0.
STUFF(char1, start, length, char2) A string with length number of characters from char1 replaced with char2, starting at start.
SUBSTRING(char, start, length) A string of length number of characters from char, from start position.
UNICODE(char) The Unicode code for the leftmost character in char.
UPPER(char) char in uppercase.

You can use the operator + to concatenate strings.

The following example uses SUBSTRING and string concatenation to present each author's first letter of the first name and then the last name:

SELECT SUBSTRING(au_fname,1,1) + '. ' + au_lname FROM authors 

Mathematical Functions

The mathematical functions in Table 26.4 perform calculations based on the input values and return a numeric value. No new mathematical functions were introduced in version 7.0.

Table 26.4. Mathematical Functions
Function Name Returns
ABS(numeric) The absolute (positive) value of numeric.
ACOS(float) The arc cosine for float.
ASIN(float) The arc sine for float.
ATAN(float) The arc tangent for float.
ATAN2(float1, float2) The arc tangent whose tangent is between float1 and float2.
CEILING(numeric) The smallest integer value that is higher than or equal to numeric.
COS(float) The trigonometric cosine of float.
COT(float) The trigonometric cotangent of float.
DEGREES(numeric) The number of degrees for a given angle, numeric, given in radians.
EXP(float) The exponential value of float.
FLOOR(numeric) The largest integer value that is lower than or equal to numeric.
LOG(float) The natural logarithm of float.
LOG10(float) The base-10 logarithm of float.
PI() The constant pi.
POWER(numeric1, numeric2) The value of numeric1 to the specified power, given in numeric2.
RADIANS(numeric) Radians of numeric, given in degrees.
RAND([seed]) A random value between 0 and 1. seed can be specified as the starting value.
ROUND(numeric, length, func) Number rounded to the specified numeric to specified length. If func is specified and not 0, numeric is rounded down to length.
SIGN(numeric) 1 if numeric is positive, 0 if numeric is 0, and ?1 if numeric is negative.
SIN(float) The trigonometric sine of float.
SQUARE(float) The square of float.
SQRT(float) The square root of float.
TAN(float) The trigonometric tangent of float.

The operators +, -, *, /, and % (modulo) are also available for numeric expressions.

Date Functions

The date functions perform operations such as formatting and subtracting. The expression given is a datetime datatype.

Some of the functions take a datepart as argument. The datepart specifies on what part of the datetime datatype you want to operate. Table 26.5 provides the codes for the datepart.

Table 26.5. Available Codes for Datepart
Datepart Abbreviation Possible 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
hour hh 0?23
minute mi 0?59
second ss 0?59
millisecond ms 0?999

The date- and time-related functions are listed in Table 26.6.

Table 26.6. Date-and Time-Related Functions
Function Name Returns New in SQL Server 2000
DATEADD(datepart, int, date) Date expression as a result of adding int dateparts to date.  
DATEDIFF(datepart, date1, date2) The number of dateparts between date1 and date2.  
DATENAME(datepart, date) The datepart of date returned as a character string. For month and day of week, the actual name is returned.  
DATEPART(datepart, date) The datepart of date returned as an integer.  
DAY(date) The day-of-month part as an integer.  
GETDATE() The current date and time.  
GETUTCDATE() The current date and time as Universal Time Coordinate Time (Greenwich Mean Time). Yes
MONTH(date) The month as an integer.  
YEAR(date) The year as an integer.  

You can use the operators + and - directly on datetime expressions in version 7.0 and later. The implied datepart is days. In this example, you use the + operator to add one day to the current date:


----------------------- ----------------------
1998-03-28 16:08:33     1998-03-29 16:08:33

Metadata Functions

The metadata functions, listed in Table 26.7, are useful for retrieving information such as column names, table names, index keys, and so on. Basically, many of the functions are shortcuts for querying the system tables.


It is better to use the system functions than to directly query the system tables. If the system tables change in forthcoming releases of SQL Server (as they did with version 7.0), your applications and scripts will still work if you use the system functions. You can also use the information schema views for retrieving system-table?related information. The views are ANSI standard and are independent of the system tables, and all have the object owner INFORMATION_SCHEMA.

Table 26.7. Metadata Functions
Function Name Returns New in SQL Server 2000
COL_LENGTH(table, column) The length of column in table.  
COL_NAME(table_id, column_id) The name of column_id in table_id.  
COLUMNPROPERTY(id, column, property) Information about a column in a table, given the table id. Returns information for a parameter, given in column, for a stored procedure. The property parameter defines the type of information to be returned.  
DATABASEPROPERTY (database_name, property) Setting of specified property for database_name. Included for backward compatibility, use DATABASEPROPERTYEX instead.  
DATABASEPROPERTYEX (database_name, property) Current setting of specified property for database_name as a sql_variant. Yes
DB_ID([db_name]) The database ID of db_name or the current database.  
DB_NAME([db_id]) The database name of db_id or the name of the current database.  
FILE_ID(filename) The ID for filename.  
FILE_NAME(file_id) The filename for file_id.  
FILEGROUP_ID(filegroupname) The ID for filegroupname.  
FILEGROUP_NAME(filegroup_id) The filegroup name for filegroup_id.  
FILEGROUPPROPERTY (filegroup_name,property) The value of property for filegroup_name.  
FILEPROPERTY (filename, property) The value of property for filename.  
FULLTEXTCATALOGPROPERTY (catalog_name, property) The value of property for full-text catalog catalog_name.  
FULLTEXTSERVICEPROPERTY (property) Information about property for full-text service-level.  
INDEX_COL(table, index_id,key_id) The column name for the specified table, index_id, and key_id.  
INDEXKEY_PROPERTY (table_ID , index_ID , key_ID , property) The property information about index key key_ID for index_ID on table_ID. Yes
INDEXPROPERTY (table_ID , index , property) The property info for index index on table_ID.  
OBJECT_ID(object_name) The ID for object_name.  
OBJECT_NAME(object_id) The database object name for object_id.  
OBJECTPROPERTY (object_id, property) Information for object_id. property defines the type of information to be returned.  
SQL_VARIANT_PROPERTY (expression,property) The property information about sql_variant expression, such as base datatype, as a sql_variant. Yes
TYPEPROPERTY(datatype, property) Information defined in property for datatype.  

System Functions

The system functions, listed in Table 26.8, are useful for retrieving information about values, options, and settings within the SQL Server.

Table 26.8. System Functions
Function Name Returns New in SQL Server 2000
APP_NAME() The name of the application that executes the function.  
CAST(expression AS datatype) The CAST function is a synonym for the CONVERT function and converts expression to datatype.  
COALESCE(expr1, [expr2,,,]) The first non-null expression in the list.  
COLLATIONPROPERTY (collation_name, property ) The specified property of the collation_name. property can be CodePAgeLCID, ComparisonStyle. Yes
CONVERT(datatype[(length)], expression, style) Converts expression to datatype. For conversion of datetime or float expressions, style defines the formatting.  
DATALENGTH(expression) The storage area of expression, including trailing blanks for character information.  
GETANSINULL([db_name]) The default nullability option of db_name for the current database.  
GETCHECKSUM(col_name) A checksum value for the values in col_name.  
HOST_ID() The process ID of the client application's process.  
HOST_NAME() The client's workstation name.  
IDENT_CURRENT('tablename') The last identity value generated for tablename by any session and within any scope. Yes
IDENT_INCR(table) The identity increment for the identity column in table.  
IDENT_SEED(table) The identity seed for the identity column in table.  
IDENTITY(datatype[,seed, increment]) AS column_name Used only in SELECT INTO to create identity column in new table.  
ISDATE(char) 1 if char is in a valid datetime format; otherwise, 0.  
ISNULL(expression, value) value if expression is NULL.  
ISNUMERIC(char) 1 if char can be converted to a numeric value; otherwise, 0.  
NEWID() A generated global unique identifier.  
NULLIF(expr1, expr2) Null if expr1 equals expr2.  
PARSENAME(object_name, object_part) Name of object_part (specified as an int) of object_name.  
PERMISSIONS(object_id[,column]) A bitmap indicating permissions on object_id and optionally column.  
ROWCOUNT_BIG() Number of rows affected by previous statement executed in session as a bigint. Yes
SCOPE_IDENTITY() The last identity value inserted into an IDENTITY column within current scope (for example, stored procedure, trigger, function, or batch). Yes
SERVER_PROPERTY('property') The property information about the server as a sql_variant. Yes
SESSIONPROPERTY(option) The SET options of a session as a sql_variant. Yes
STATS_DATE(table_id, index_id) Date when the distribution page was updated for index_id on table_id.  
TRIGGER_NESTLEVEL ([tr_object_id]) Nesting level of specified or current trigger.  

The following example returns the title ID and price for all books. If the price is not set (NULL), it returns a price of 0:

SELECT title_id, ISNULL(price, 0) FROM titles 

Let us expand the example. You want to display the string 'Not Priced' for those that contain NULL values. You have to convert the price to a character value before replacing NULL with your text string:

SELECT title_id, ISNULL(CONVERT(CHAR(10),price), 'Not Priced') 
FROM titles

Security Functions

The security functions, listed in Table 26.9, are useful for retrieving information about users and roles.

Table 26.9. Security Functions
Function Name Returns New in SQL Server 2000
HAS_DBACCESS(database) Information about whether current user has access to database. Yes
IS_MEMBER(group | role] 1 if the user is a member of specified NT group or SQL Server role; otherwise, 0.  
IS_SRVROLEMEMBER (role [, login]) 1 if the user's login ID is a member of the specified server role; otherwise, 0. An explicit login name can be specified.  
SUSER_ID(login_name) The loginid of the specified login_name. Included for backward compatibility; use SUSER_SID instead.  
SUSER_NAME([login_id]) The login name of login_id. Included for backward compatibility; use SUSER_SNAME instead.  
SUSER_SID([login]) Security identification number (SID) for login.  
SUSER_SNAME([login_id]) The login name of login_id.  
USER_ID([username]) The user ID for username.  
USER_NAME([user_id]) The username for current user or user_id.  

The following example returns the current user's database username, login name, and whether the user is a member of the db_owner role:

select substring (user_name(), 1, 20) AS 'user_name', 
       substring (suser_sname(),1, 30) AS 'login_name',
       case IS_MEMBER('db_owner')
          when 1 then 'Yes'
          else 'No'
          end AS 'Is_dbOwner'

user_name            login_name                     Is_dbOwner
-------------------- ------------------------------ ----------
dbo                  RRANKINSA20P\rrankins          Yes

Text and Image Functions

These scalar functions, listed in Table 26.10, perform an operation on a text or image input value or column and return information about the value.

Table 26.10. Text and Image Functions
Function Name Returns
TEXTPTR (column_name) The text-pointer for a text or image column as a varbinary(16) value. Pointer can be used in READTEXT, WRITETEXT, and UPDATETEXT statements.
TEXTVALID('table.column', ptr) 1 if text or image pointer ptr is valid for table.column, 0 if pointer is invalid.

The following example shows how to use the TEXTPTR function to retrieve a text pointer and use it in the READTEXT command:

USE pubs 
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(pr_info)
   FROM pub_info
   WHERE pub_id = '0877'
READTEXT pub_info.pr_info @ptr 0 65

This is sample text data for Binnet & Hardley, publisher 0877 in

Rowset Functions

The rowset functions listed in Table 26.11 return an object that can be used in place of a table reference in a Transact-SQL statement.

Table 26.11. Rowset Functions
Function Name Returns New in SQL Server 2000
CONTAINSTABLE (table, column| * } , 'contains_conditions' [,top_n_by_rank]) A table of 0 or more rows for columns containing string data using precise or fuzzy matches to a single word or phrases, the proximity of words to one another, or weighted matches, as specified in contains_conditions. Result can be limited to the top n matching rows ordered by rank.  
FREETEXTTABLE (table, { column | * } , 'freetext_ string' [ , top_n_by_rank ] ) A table of 0 or more rows that match the meaning of the text in freetext_string. table is a table marked for full-text querying. column must be columns that contain string data. Result can be limited to the top n matching rows ordered by rank.  
OPENDATASOURCE (provider_name, init_string) The connection information used as the first part (servername) of a four-part fully qualified object name. Can be used in place of a linked server name. Should only reference OLE DB data sources. Yes
OPENQUERY (linked_server, 'query') Resultset from specified pass-through query on linked_server.  
OPENROWSET (provider_name, {'datasource';'user_id'; 'password'|'provider_string'} , {[catalog.][schema.]object | 'query'} ) Remote data result from specified connection to OLE DB data source.  
OPENXML(docid, rowpattern, flag) [WITH (schemaDeclaration | tablename)] Resultset from an XML document specified with docid. Data is returned in edge table format unless SchemaDeclaration or TableName is specified. Yes

The following example uses the OPENROWSET function and the Microsoft OLE DB Provider for SQL Server to titles records from the titles table in the pubs database on a remote server named RRANKINSA20P. Notice how the result from the function can be used just like a table, even in a JOIN clause:

select p.pub_id, t.title 
   from publishers p
                     'SELECT * FROM pubs.dbo.titles ORDER BY pub_id') AS t
     on p.pub_id = t.pub_id

pub_id title
------ ------------------------------------------------------------------------
0736   You Can Combat Computer Stress!
0736   Is Anger the Enemy?
0736   Life Without Fear
0736   Prolonged Data Deprivation: Four Case Studies
0736   Emotional Security: A New Algorithm
0877   Silicon Valley Gastronomic Treats
0877   The Gourmet Microwave
0877   The Psychology of Computer Cooking
0877   Computer Phobic AND Non-Phobic Individuals: Behavior Variations
0877   Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
0877   Fifty Years in Buckingham Palace Kitchens
0877   Sushi, Anyone?
1389   The Busy Executive's Database Guide
1389   Cooking with Computers: Surreptitious Balance Sheets
1389   Straight Talk About Computers
1389   But Is It User Friendly?
1389   Secrets of Silicon Valley
1389   Net Etiquette

To learn more about the CONTAINSTABLE and FREETEXTTABLE functions, see Chapter 44, "SQL Server Full-Text Search Services." For more information on using XML documents and the OPENXML function, see Chapter 41, "Using XML in SQL Server 2000."

Niladic Functions

The niladic group of functions, list

Part III: SQL Server Administration
Part IV: Transact-SQL
Part V: SQL Server Internals and Performance Tuning
Part VI: Additional SQL Server Features