Functions: Simple Functions
Simple Functions (scalar functions) are generally included in most
SQL dialects as an extension to the
SQL Standard. They operate on column values in a single row and produce a single value which is included in the result set. Local
SQL has several such functions, including:
CAST Convert values from one data type to another. For example, convert a number to a character.
EXTRACT Extract the year, month, or day from a date as an integer.
SUBSTRING Extract a portion of a character column.
UPPER Convert a character column value to all uppercase letters.
LOWER Convert a character column value to all lowercase letters.
TRIM Removes any leading spaces, trailing spaces, or both from a character column value.
Note that these functions are not available with QBE.
Suppose we need to generate a list of all company names, but instead of mixed caps, we need them in all upper case. We can generate a result set containing a capitalized list of all company names using the following
query:
SELECT Upper(Company) Company
FROM Customer
Our ANSWER table will have a single column containing the Company name for each company, all in uppercase characters. In the above example, we explicitly named the new column ‘COMPANY’.
These functions are described in detail in the LocalSQL.HLP file. They will be explored in detail in a later article.
Summary
The
SQL SELECT statement creates a result set (i.e., an ANSWER table) consisting of specific rows and columns of a table. The result set can consist of simple columns derived from the queried table, calculated expressions or the result of a
SQL function. There are two types of
SQL functions, aggregate functions, which operate across multiple rows, and simple functions, which operate on single rows. Result set columns may have different names assigned to them during the
SQL query using the AS operator in the
query.