Functions (Java Enterprise in a Nutshell)

8.5. Functions

SQL is not a procedural language, but it does provide some data transformation capabilities. In addition to the string concatenation operator (||), the SQL-92 specification defines two sets of functions: aggregate functions and value functions.

8.5.1. Aggregate Functions

In the section on the SELECT statement, we saw an aggregate function used to count the number of records within a group. In the main, this is what aggregate functions do: they act on all the records of query, counting rows, averaging fields, and so forth. For example, here's how to count the number of rows returned by a SELECT statement:

SELECT COUNT(*) FROM CUSTOMERS

Instead of returning each row of the CUSTOMERS table, this query returns a single-column, single-row result that contains the number of records in CUSTOMERS.

The other aggregate functions are AVG, SUM, MAX, and MIN. Unlike COUNT, which works on either a single column or all columns, the other functions work only on a single column. AVG and SUM can be applied against numerical data types only (integers, reals, etc.), while MAX and MIN work with any data type. Here are some examples:

SELECT MIN(AGE) FROM GUESTS
SELECT MAX(NAME) FROM GUESTS
SELECT AVG(AGE), SUM(AGE) FROM GUESTS

8.5.2. Value Functions

Value functions work on particular column values and return modified data. Some of them also generate values from system information.

8.5.2.1. Date/time functions

There are three date and time functions that retrieve the current date, current time, and current timestamp, respectively, from the database:

CURRENT_DATE
CURRENT_TIME[(precision)]
CURRENT_TIMESTAMP[(precision)]

CURRENT_TIME and CURRENT_TIMESTAMP accept an optional precision level, which specifies the decimal fractions of a second to be included in the time portion of the value. The current time zone is used with all these functions.

Here's how you might use these functions in a query:

SELECT * FROM ORDERS WHERE ORDER_DATE = CURRENT_DATE
INSERT INTO VISITORS (VISIT_TS) VALUES (CURRENT_TIMESTAMP)

Some databases have platform-specific commands that duplicate this functionality (these commands often predate SQL-92). Oracle's SYSDATE is one example. Note that these functions are not required for entry-level SQL-92.

8.5.2.2. String manipulation functions

The concatenation operator, ||, has been around since before the SQL-92 standard. It allows you to concatenate multiple column values and string literals. Say we have a table that contains FIRST_NAME and LAST_NAME fields, and we want to display them in a "last, first" form. Here's a SQL statement that returns a single column that does just that:

SELECT LAST_NAME || ', ' || FIRST_NAME FROM CUSTOMERS

In addition, the SQL-92 standard defines a number of other functions that can be used in SQL statements. UPPER and LOWER convert a column into uppercase or lowercase, respectively:

SELECT UPPER(LAST_NAME) FROM CUSTOMERS
SELECT LOWER(FIRST_NAME) FROM CUSTOMERS

These functions can also be used in WHERE predicates, for example, to produce a case-insensitive search:

SELECT * FROM CUSTOMERS WHERE UPPER(FIRST_NAME) LIKE 'WILL%'

Most databases support UPPER and LOWER, but they are only required for full SQL-92 conformance, not entry-level conformance.

The TRIM function removes characters from one or both ends of a string:

TRIM ([ [ LEADING | TRAILING | BOTH ] [ character ] FROM ] string )

Calling TRIM on a string trims leading and trailing whitespace. Here's how to trim just leading blanks:

SELECT TRIM(LEADING ' ' FROM FIRST_NAME) FROM CUSTOMERS

And here's how to trim all "-" characters from both sides of a string:

SELECT TRIM(BOTH '-' FROM FIRST_NAME) FROM CUSTOMERS

Like UPPER and LOWER, TRIM is only required for full SQL-92 conformance, although it is supported by most database implementations.

The SUBSTRING command extracts a given number of characters from a larger string. It is defined as:

SUBSTRING (source_string FROM start_pos FOR number_of_characters)

For example, to get each customer's initials, we might use the following query:

SELECT SUBSTRING (FIRST_NAME FROM 1 FOR 1), SUBSTRING(LAST_NAME FROM 1 FOR 1)

SUBSTRING is only required for intermediate level SQL-92 conformance.