Functions for Expressions

See Also      Tasks

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

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.

If you are creating queries (not views, stored procedures, or triggers) that might be run against different databases, you can also use ODBC functions, which are supported by virtually all database drivers no matter what database you are querying. ODBC syntax includes the "fn" qualifier in front of the function name and braces around the entire function. For example, the following expression uses an ODBC function to convert text to lowercase letters:

{fn LCASE ( address ) }

The Query 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. For details about ODBC functions, refer to the Open Database Connectivity (ODBC) SDK in the Microsoft Developer Network or to the Microsoft ODBC 2.0 Programmer’s Reference and SDK Guide.

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 and Grouping.

String Functions

The following functions for manipulating character strings provide a sampling of those that are available in ODBC and in many databases, such as Microsoft SQL Server.

SELECT UPPER(substring(lname, 1, 1)) + 
  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.

SELECT stor_name, LTRIM(stor_address)
FROM stores


Displays an address column after extraneous spaces are removed from the front.

SELECT SUBSTRING(phone,1,3)
FROM employee


Displays the first three characters (the area code) of a phone number.

SELECT * FROM employee
WHERE UPPER(lname) = 'SMITH'


Converts the contents of the lname column to uppercase before comparing them to a specific value (avoids mismatches if the search is case sensitive). For details about case sensitivity in SQL Server, see Query Designer Considerations .

¹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 either ODBC or in databases such as Microsoft SQL Server and Microsoft Access.

Note   Oracle uses the TO_DATE function to format dates. For details, see Query Designer Considerations for Oracle Databases.

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


Locates all employees hired more than five years ago.

SELECT DATEPART(year, hire_date)
FROM employee


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

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

¹If calling as an ODBC function, use syntax such as: { fn CURDATE() }.

Mathematical Functions

ODBC and most databases provide some mathematical functions that you can use when performing calculations. The following functions are typical of those available in many databases, but you should refer to the ODBC documentation and documentation for the database for details about what functions are available and what arguments they take.

Note   You can use the aggregate functions AVG( ), COUNT( ), MAX( ), MIN( ), and SUM( ) to create averages and totals in your report. For details, see Summarizing and Grouping.

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


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

UPDATE titles
SET price = FLOOR(price)


Rounds all prices in the titles table down 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

ODBC and 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, but refer to the ODBC documentation or documentation for the database for details about what functions are available and what arguments they take.

SELECT DATALENGTH(au_lname + ', ' 
  + au_fname)
FROM authors


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

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


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

¹If calling as  an ODBC function, use syntax such as: { fn USER() }.

Other Functions

In addition to the functions listed earlier, ODBC and most 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.

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.

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


Searches for names that sound like "Michael".

SELECT str(job_id) + ' ' + 
   str(job_lvl)
FROM employee


Displays the job_id and job_lvl columns (both numeric) in a single string.