Transact SQL scalar functions for expressions (ADP)

Microsoft Office Access 2003

previous page next page

You can call a number of scalar 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

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

The following information is a list of commonly-used scalar functions. For a complete list of SQL Server scalar functions, see the Transact SQL reference Functions help topic in the Microsoft SQL Server documentation

Note   You can also use a special set of functions, the aggregate functions such as SUM( ) and AVG( ), to create queries that summarize data.

ShowString functions

Function Description Example
LCASE( ), LOWER( ) Converts strings to lowercase 
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.
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 (avoids mismatches if the search is case sensitive).

ShowDate functions

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.

ShowMathematical functions

Note   You can also use aggregate functions, such as 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, 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.

ShowSystem functions

Function Description Example
DATALENGTH( ) Returns the number of bytes used by the specified expression 
SELECT DATALENGTH(au_lname + ', ' 
 + au_fname)
FROM authors

Lists the number of bytes required for the combination of last and first names.
USER( ),
USER_NAME( )		 Returns the current user name

Security  Use this feature with caution. Sensitive or confidential information could be revealed to other users.

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

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

ShowOther functions

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) + ' ' + 
 str(job_lvl)
FROM employee

Displays the job_id and job_lvl columns (both numeric) in a single string.
previous page start next page