SQL
Functions
Functions
are a very powerful feature of SQL. They can be used to do the following:
•
Perform
calculations on data
•
Modify
individual data items
•
Manipulate
output for groups of rows
•
Format
dates and numbers for display
•
Convert
column data types
SQL
functions sometimes take arguments and always return a value.
Two
Types of SQL Functions
There are
two types of functions:
•
Single-row
functions
•
Multiple-row
functions
Single-Row
Functions
These
functions operate on single rows only and return one result per row. There are
different types of single-row functions.
•
Character
•
Number
•
Date
•
Conversion
•
General
Multiple-Row
Functions
Functions
can manipulate groups of rows to give one result per group of rows. These
functions are also known as group functions
Single-Row
Functions
Single-row
functions are used to manipulate data items. They accept one or more arguments
and return one value for each row that is returned by the query. An argument
can be one of the following:
•
User-supplied
constant
•
Variable
value
•
Column
name
•
Expression
Features of
single-row functions include:
•
Acting
on each row that is returned in the query
•
Returning
one result per row
•
Possibly
returning a data value of a different type than the one that is referenced
•
Possibly
expecting one or more arguments
•
Can
be used in SELECT, WHERE, and ORDER BY clauses; can be nested
In the
syntax:
function_name is
the name of the function
arg1,
arg2 is any argument to be used by the
function. This can be represented
by a column name or expression.
•
Character
functions: Accept
character input and can return both character and number values
•
Number
functions: Accept
numeric input and return numeric values
•
Date
functions: Operate
on values of the DATE data type (All date functions return a value of the DATE
data type except the MONTHS_BETWEEN function, which returns a number.)
•
Conversion
functions: Convert
a value from one data type to another
General
functions:
-
NVL
-
NVL2
-
NULLIF
-
COALESCE
-
CASE
-
DECODE
Character
Functions
Single-row
character functions accept character data as input and can return both
character and numeric values. Character functions can be divided into the
following:
•
Case-conversion
functions
•
Character-manipulation
functions
Case-Conversion
Functions
LOWER,
UPPER, and INITCAP are the three case-conversion functions.
• LOWER: Converts mixed-case or
uppercase character strings to lowercase
• UPPER: Converts mixed-case or
lowercase character strings to uppercase
• INITCAP: Converts the first letter
of each word to uppercase and the remaining letters to lowercase
SELECT 'The job id for
'||UPPER(last_name)||' is '
||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;
Using
Case-Conversion Functions
The WHERE
clause of SQL statement specifies that the employee name in the EMPLOYEES table
is compared to higgins, converting the LAST_NAME column to lowercase for
comparison purposes. Because both names are now lowercase, a match is found and
one row is selected. The WHERE clause can be rewritten in the following manner
to produce the same result:
...WHERE last_name =
'Higgins'
The name in
the output appears as it was stored in the database. To display the name in
uppercase, use the UPPER function in the SELECT statement.
SELECT employee_id,
UPPER(last_name), department_id
FROM employees
WHERE INITCAP(last_name) = 'Higgins';
Character-Manipulation
Functions
• CONCAT: Joins values together (You
are limited to using two parameters with CONCAT.)
• SUBSTR: Extracts a string of determined
length
• LENGTH: Shows the length of a string
as a numeric value
• INSTR: Finds the numeric position of
a named character
• LPAD: Returns an expression
left-padded to the length of n characters with a character expression
• RPAD: Returns an expression right-padded
to the length of n characters with a character expression
• TRIM: Trims leading or trailing
characters (or both) from a character string (If trim_character or trim_source
is a character literal, you must enclose it within single quotation marks.)
Note: You can use functions such as UPPER
and LOWER with ampersand substitution. For example, use
UPPER('&job_title')so that the user does not have to enter the job title in
a specific case.
Using
the Character-Manipulation Functions
The example
displays employee first names and last names joined together, the length of the
employee last name, and the numeric position of the letter “a” in the employee
last name for all employees who have the string, REP, contained in the job ID
starting at the fourth position of the job ID.
Example:
SQL
statement to display the data for those employees whose last names end with the
letter “n.”
SELECT employee_id,
CONCAT(first_name, last_name) NAME,
LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(last_name, -1, 1) = 'n';
Number
Functions
Number
functions accept numeric input and return numeric values. This section
describes some of the number functions.
Using
the ROUND Function
The ROUND
function rounds the column, expression, or value to n decimal places. If
the second argument is 0 or is missing, the value is rounded to zero decimal
places. If the second argument is 2, the value is rounded to two decimal
places. Conversely, if the second argument is –2, the value is rounded to two
decimal places to the left (rounded to the nearest unit of 100).
The ROUND
function can also be used with date functions.
DUAL
Table
The DUAL
table is owned by the user SYS and can be accessed by all users. It contains
one column, DUMMY, and one row with the value X. The DUAL table is useful when
you want to return a value only once (for example, the value of a constant,
pseudocolumn, or expression that is not derived from a table with user data).
The DUAL table is generally used for completeness of the SELECT clause syntax,
because both SELECT and FROM clauses are mandatory, and several calculations do
not need to select from the actual tables.
Using
the TRUNC Function
The TRUNC
function truncates the column, expression, or value to n decimal places.
The TRUNC
function works with arguments similar to those of the ROUND function. If the
second argument is 0 or is missing, the value is truncated to zero decimal
places. If the second argument is 2, the value is truncated to two decimal
places. Conversely, if the second argument is –2, the value is truncated to two
decimal places to the left. If the second argument is –1, the value is
truncated to one decimal place to the left.
Like the
ROUND function, the TRUNC function can be used with date functions.
Using
the MOD Function
The MOD
function finds the remainder of the first argument divided by the second
argument.
Note: The MOD function is often used to
determine whether a value is odd or even.
