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
18.104.22.168. 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.
22.214.171.124. 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.
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.
Copyright © 2001 O'Reilly & Associates. All rights reserved.