SQL in a Nutshell, 3rd Edition

ANSI SQL Scalar Functions

The ANSI SQL scalar functions return a single value each time they are invoked. The SQL standard provides many scalar functions that can be used to manipulate date and time types, strings, and numbers, as well as to retrieve system information such as the current user or login name. Scalar functions fall into the categories listed in Table 4-2.

Table 4-2. Categories of scalar functions

Function category	Explanation
Built-in	Performs operations on values or settings built into the database. Oracle uses the term “built-in” to describe all the specialty functions that it provides and that are thus “built into” the DBMS. This is a distinct and separate usage from the “built-in” scalar functions described here.
CASE and CAST	While these two functions operate on scalar input values, they are in a category all their own. CASE supplies IF/THEN logic to SQL statements, and CAST can convert values from one datatype to another.
Date and time	Performs operations on temporal datatypes and returns values in a temporal datatype format. There is no SQL2003 function that operates on a temporal datatype and returns a temporal result. The closest function is EXTRACT (covered in “Numeric Scalar Functions” later in this chapter), which operates on temporal values and returns numeric values. Functions returning temporal values but operating on no arguments are covered in the later section “Built-in Scalar Functions.”
Numeric	Performs operations on numeric values and returns numeric values.
String	Performs operations on character values (e.g., CHAR, VARCHAR, NCHAR, NVARCHAR, and CLOB) and returns string or numeric values.