Functions

Aside from using operators to manually construct expressions, SQL provides built-in functions you can use.

TIP

Functions are small blocks of code that can perform operations and return a value.

Functions are available simply by making a call to them and passing the value and/or values on which you want the function to operate.

NOTE

The functions outlined in the next sections represent a generic list of SQL functions. It's important to realize that not all databases support the same functions. Although some databases support similar functions, the way the function is written can differ syntactically from database to database. In the next sections, I'll provide you with a broad list of these functions. It's up to you however, to consult your database documentation for the appropriate syntax variation for the function.

Date and Time Functions

Date and Time functions allow for manipulations using dates and times that are stored within your database. For instance, if you wanted to return all items from the Orders table that were purchased on June 30, 2005, you might write the following code:

 SELECT *  FROM Orders  WHERE DatePurchased LIKE '6/30/2005' 

This code would produce the following results:

OrderID	EmployeeID	ItemID	Quantity	DatePurchased
24	3	2	1	6/30/05

If you wanted to find all the orders from the previous month, you could use the DateAdd() function:

 SELECT *  FROM Orders WHERE DatePurchased > DateAdd(m, -1, Date()) 

Assuming that the current date was 6/30/05, the results would be as follows:

OrderID	EmployeeID	ItemID	Quantity	DatePurchased
24	3	2	1	6/15/05
2	2	2	1	6/11/05
11	6	3	1	6/14/05

TIP

Parameters are values that you pass into the function so that it knows what to do or how to return the value.

Also notice that the DateAdd() function accepts parameters. These parameters include the following:

• This parameter specifies which part of the date/time object you want to work with. Typically, you would want to use one of a few values: m for month, w for week, d for day, h for hour, n for minute, s for second, and so forth.

• How much time to add or subtractin the preceding example, I subtracted one month.

• The date you want to use. In the preceding example, I called another functionthe system dateas the date I wanted to use. When you use the Date() function, you are effectively reading the date and time from the computer and passing it in as a value.

There are many other Date and Time functions. Too many, in fact, to cover in this small section. Date and Time functions are among the widely used functions in SQL and are worth the research.

The Count() Function

One of the most obvious functions available is the Count() function. The Count() function is used when you want to perform a count of records. Consider the following data from the Orders table:

OrderID	EmployeeID	ItemID	Quantity	DatePurchased
24	3	2	1	6/30/05
2	2	2	2	6/30/05
11	6	3	2	6/30/05

You could use the following code to count the number of orders you have taken in a day from the Orders table:

 SELECT Count(Quantity) AS NumberOfOrders FROM Orders 

The statement would result in the following:

NumberOfOrders

3

Notice that you pass in the field name as a parameter in the Count() function. The parameter is evaluated, and a value is returned into a virtual field named NumberOfOrders.

The Sum() Function

Unlike the Count() function that returns a value from a calculation on the number of fields, the Sum() function performs a calculation on data within those fields. If, for instance, you needed to know the total number of items you sold, you could modify the preceding statement to read:

 SELECT Sum(Quantity) AS Total  FROM Orders 

The statement would produce the following results:

Total

5

Rather than simply doing a count on the records, the sum is calculated based on the values within the specified field. Because a total of 5 items were ordered, this value is shown.

The Avg() Function

The Avg() function returns the average of values in specific fields. Consider the following orders in the Orders table:

OrderID	EmployeeID	ItemID	Quantity	DatePurchased
24	3	2	1	6/30/05
2	2	2	3	6/30/05
11	6	3	5	6/30/05

To get the total average of items being ordered, we might write a statement that resembled the following:

 SELECT Avg(Quantity) AS Average  FROM Orders 

The statement would produce the following result:

Average

3

Of course, this is because the average of the numbers 1, 3, and 5 is 3.

The Min() and Max() Functions

The Min() and Max() functions enable you to find the smallest and largest values of a specific record. To get the minimum quantity ordered, you could write a statement such as this one:

 SELECT Min(Quantity) AS Minimum  FROM Orders 

Based on the Orders table data from previous section, the preceding statement produces this result (because the minimum value in the Quantity field is 1):

Minimum

1

To receive the maximum value of a record in the database, try this statement:

 SELECT MAX(Quantity) AS Maximum  FROM Orders 

Based on the Orders table data from previous section, the preceding statement produces this result (because the maximum value in the Quantity field is 5):

Maximum

5

Arithmetic Functions

Aside from using Sum(), Min(), Max(), and Avg(), a few other arithmetic functions can help you when calculating fields in your database. They are as follows:

Function	Description
Abs()	Returns the absolute value.
Ceil()	Returns the largest integer value not greater than the value.
Floor()	Returns the smallest integer value not greater than the value.
Cos()	Returns the cosine of the value where the value is provided in radians.
Cosh()	Returns the hyperbolic cosine of the value where the value is provided in radians.
Sin()	Returns the sine of the value where the value is provided in radians.
Sinh()	Returns the hyperbolic sine of the value where the value is provided in radians.
Tan()	Returns the tangent of the value where the value is provided in radians.
Tanh()	Returns the hyperbolic tangent of the value where the value is provided in radians.
Exp()	Returns the mathematical constant e raised to the provided exponential value.
Mod()	Returns the remainder of a value divided by a second value.
Sign()	Returns the sign of the argument as 1, 0, or 1, depending on whether the value is negative, zero, or positive.
Sqrt()	Returns the non-negative square root of a value.
Power()	Returns the result of a value raised to the power of a second value.
Ln()	Returns the natural logarithm of a value.
Log()	Returns the logarithm of a value in the base of a second value.

String Functions

String functions are similar to other functions except that they work with literal text values rather than numerical values. The following string functions are the most common:

Function	Description
Chr()	Converts an ASCII value to its string equivalent.
Concat()	Concatenates (merges) two strings into one.
Initcap()	Capitalizes the first letter of each word in provided string.
Upper()	Returns the provided string in all uppercase.
Lower()	Returns the provided string in all lowercase.
Lpad()	Returns a value padded on the left based on the numerical value you specify.
Rpad()	Returns a value padded on the right based on the numerical value you specify.
Ltrim()	Trims a specified amount of space or characters off the left side of a string.
Rtrim()	Trims a specified amount of space or characters off the right side of a string.
Replace()	Changes a portion of the string with a value that you specify. Replace() takes three values: string, target, and replacement string.
Substr()	Returns the substring of a value that begins at a positive value and is certain number of characters long. Substr() takes three values: string, position, and length.
Length()	Returns the string length in number of characters.