Functions for Expressions

Article
12/03/2014

Functions are stored operations you can use. Database management systems provide basic functions, or you can create your own user-defined functions.

You can call a number of functions when you are building an expression, including:

String (character) functions.
Date functions.
Mathematical functions.
System functions.
Other functions, such as those to convert data from one type to another.
User-defined functions.

In general, if you are familiar with the functions available in your database, you can use the function names and syntax supported by that database. In some cases, such as when you are creating views, stored procedures, or triggers, you must use database-specific function names and syntax.

The Query and View Designer can help you work with functions by:

Correctly inserting quotation marks in function arguments.
Validating the data types of arguments.
Validating the data types of return values.

For details about functions supported by the database, refer to the documentation for the database

Note

You can use a special set of functions, the aggregate functions such as SUM( ) and AVG( ), to create queries that summarize data. For details, see Summarizing Query Results.

String Functions

The following functions for manipulating character strings provide a sampling of those that are available in many databases.

Function	Description	Example
LCASE( )*, LOWER( )	Converts strings to lowercase.	To capitalize the first character use: SELECT UPPER(substring(lname, 1, 1)) To convert the remaining characters to lower case use: LOWER(substring (lname, 2, 99)) `FROM employee` Displays a last name after the first character is converted to uppercase and the remaining characters to lowercase.
LTRIM( )	Removes leading spaces from a string.	`SELECT stor_name, LTRIM(stor_address) FROM stores` Displays an address column after extraneous spaces are removed from the front.
SUBSTRING( )	Extracts one or more characters from a string.	`SELECT SUBSTRING(phone,1,3) FROM employee` Displays the first three characters (the area code) of a phone number.
UCASE( )*, UPPER( )	Converts strings to uppercase.	`SELECT * FROM employee WHERE UPPER(lname) = 'SMITH'` Converts the contents of the lname column to uppercase before comparing them to a specific value to avoid mismatches if the search is case sensitive.

* If calling as an ODBC function, use syntax such as: { fn LCASE(text) }.

Date Functions

The following functions (or others similar to them) are available in many databases.

Function	Description	Example
DATEDIFF( )	Calculates an interval between two dates.	`SELECT fname, lname, hire_date FROM employee WHERE DATEDIFF(year, hire_date, getdate()) > 5` Locates all employees hired more than five years ago.
DATEPART( )	Returns the specified portion of a date or datetime column, including the day, month, or year.	`SELECT DATEPART(year, hire_date) FROM employee` Displays only the year in which an employee was hired (not the full date).
CURDATE( )*, GETDATE( ) or DATE( )	Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today.	`SELECT order_id FROM orders WHERE order_date = GETDATE() Displays orders placed today.`

Function

Description

Example

DATEDIFF( )

Calculates an interval between two dates.

SELECT fname, lname, hire_date
FROM employee
WHERE DATEDIFF(year, hire_date, getdate()) > 5

Locates all employees hired more than five years ago.

DATEPART( )

Returns the specified portion of a date or datetime column, including the day, month, or year.

SELECT DATEPART(year, hire_date)
FROM employee

Displays only the year in which an employee was hired (not the full date).

CURDATE( )*, GETDATE( ) or DATE( )

Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today.

SELECT order_id
FROM orders
WHERE order_date = GETDATE()
Displays orders placed today.

Mathematical Functions

Most databases provide some mathematical functions that you can use when performing calculations. The following functions are typical of those available in many databases.

Note

You can use the aggregate functions AVG( ), COUNT( ), MAX( ), MIN( ), and SUM() to create averages and totals in your report.

Function	Description	Example
ROUND( )	Rounds a number off to the specified number of decimal places	`SELECT ROUND(qty * (price * discount), 2) FROM sales` Displays a total price based on a discount, and then rounds the results off to two decimal places.
FLOOR( )	Rounds a number down to the nearest (smallest) whole number	`UPDATE titles SET price = FLOOR(price)` Rounds all prices in the titles table down to the nearest whole number.
CEILING( )	Rounds a number up to the nearest whole number	`INSERT INTO archivetitle SELECT title, CEILING(price) FROM titles` Copies the title and the price (rounded up to the nearest integer) from the titles table to the archivetitle table.

Function

Description

Example

ROUND( )

Rounds a number off to the specified number of decimal places

SELECT ROUND(qty * (price * discount), 2)
FROM sales

Displays a total price based on a discount, and then rounds the results off to two decimal places.

FLOOR( )

Rounds a number down to the nearest (smallest) whole number

UPDATE titles
SET price = FLOOR(price)

Rounds all prices in the titles table down to the nearest whole number.

CEILING( )

Rounds a number up to the nearest whole number

INSERT INTO archivetitle
SELECT title, CEILING(price)
FROM titles

Copies the title and the price (rounded up to the nearest integer) from the titles table to the archivetitle table.

System Functions

Most databases make available special functions that you can use to return information about the current database, current user, or the server. The following functions are typical of those available in many databases.

Function	Description	Example
DATALENGTH( )	Returns the number of bytes used by the specified expression.	`SELECT DATALENGTH(au_lname) FROM authors` Lists the number of bytes required for the last and first names.
USER( )*, USER_NAME( )	Returns the current user name.	`SELECT company_name, city, phone FROM customers WHERE salesperson = USER_NAME()` Creates a list of customers for the salesperson who runs the query.

Function

Description

Example

DATALENGTH( )

Returns the number of bytes used by the specified expression.

SELECT DATALENGTH(au_lname)
FROM authors

Lists the number of bytes required for the last and first names.

USER( )*, USER_NAME( )

Returns the current user name.

SELECT company_name, city, phone
FROM customers
WHERE salesperson = USER_NAME()

Creates a list of customers for the salesperson who runs the query.

Other Functions

In addition to the functions listed earlier, some databases provide other functions to help you create queries, such as data type conversion or other utility functions. The following functions illustrate the type of utility functions that are available in many databases.

Function	Description	Example
CONVERT( )	Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type.	`SELECT 'Hired: ' + CONVERT(char (11), hire_date) FROM employee` Displays a date with a caption in front of it; the CONVERT( ) function creates a string out of the date so that it can be concatenated with a literal string.
SOUNDEX( )	Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches.	`SELECT au_lname, au_fname FROM authors WHERE SOUNDEX(au_fname) = 'M240'` Searches for names that sound like "Michael."
STR( )	Converts numeric data into a character string so you can manipulate it with text operators.	`SELECT str(job_id) FROM employee` Displays the job_id column as a single string.

Function

Description

Example

CONVERT( )

Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type.

SELECT 'Hired: ' + CONVERT(char (11),
  hire_date)
FROM employee

Displays a date with a caption in front of it; the CONVERT( ) function creates a string out of the date so that it can be concatenated with a literal string.

SOUNDEX( )

Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches.

SELECT au_lname, au_fname
FROM authors
WHERE SOUNDEX(au_fname) = 'M240'

Searches for names that sound like "Michael."

STR( )

Converts numeric data into a character string so you can manipulate it with text operators.

SELECT str(job_id)
FROM employee

Displays the job_id column as a single string.

Share via

Functions for Expressions

String Functions

Date Functions

Mathematical Functions

System Functions

Other Functions

See Also

Reference

Concepts

Other Resources

Additional resources