Data Types
See
Appendix B for a cross reference of SQL functions by database vendor.
SQL provides a
very large list of built in functions. Each vendor also extends this
list of functions with their own. In this section, I will describe
some of the more commonly available SQL functions. It is important
to remember that functions, like data types, are very vendor
dependent. Vendors feel free to use different names for functions
than what the SQL standard calls for. The best way to see if a
particular function is available is to check your vendor's
documentation. If you don't find a particular function, look for
names similar.
The list below is not a list of all possible
functions, nor is it a list of all SQL Standard functions. It is a
list of functions that you will commonly run across. This list is to
assist you and give you an idea about what kind of functionality you
may have available to you. As always, check your vendor
documentation for exact syntax. See
Appendix B for a cross reference of SQL functions by database vendor.
String
Functions
SUBSTR –
Extracts parts of a string
ASCII –
convert a string to its ASCII codes
CHR –
Convert ASCII code to a string
COALESCE, NVL –
Test for null and optionally return a non-null result
CONCATENATE, ||
- Concatenate two strings to a single string
INSTR –
Finds the location of a specific character or substring within a
string
UPPER, LOWER,
INITCAP – Convert a string to uppercase, lowercase or initial
caps
LPAD, RPAD –
Pad a string with a character or string. Lpad puts the pad
character(s) before the string, rpad puts the pad character(s) after
the string
USER,
CURRENT_USER, SYSTEM_USER – Return the current user
LENGTH –
Return the length of a string
Numeric
Functions
SQL provides
plenty of standard math functions such as ACOS, COS, TAN, ATAN, ASIN,
CEILING, FLOOR, etc. They work like the math concepts you may have
studied in school. Most are self explanatory.
In addition, SQL
provides ROUND and TRUNC. ROUND will round a number to the nearest
integer (or decimal position), and TRUNC(ATE) will truncate a number
at the chosen decimal position.
Date
Functions
CURRDATE,
CURRTIME, SYSDATE, NOW – Returns the current date or time.
ADDMONTHS,
ADDYEARS – Add months or years to a date. You can add days to
a Date field by using the + operator. SYSDATE + 1 adds 1 day to the
current system date.
MONTHS_BETWEEN –
Return a the number of days (fractional) between two dates. You can
get the number of days between two dates by subtracting them.
SYSTIMESTAMP,
CURRTIMESTAMP, TIMESTAMP – Returns current date and time as a
timestamp.
Conversion
Functions
TO_CHAR –
Convert a number or date to a string. Usually takes a format mask to
format the output to your preference.
TO_DATE –
Convert a string to a date.
CAST, CONVERT –
Cast (Convert) one data type to another
TO_NUMBER –
Convert a string to a number
TO_TIMESTAMP –
Convert a string to a timestamp
Aggregate
Functions
AVG –
Returns the average value of a column over a result set
SUM –
Returns a sum of a column over a result set
COUNT –
Returns a count of rows over a result set
FIRST –
Returns the first value of a column in a result set
LAST –
Returns the last value of a column in a result set
MIN –
Returns the minimum value of a column over a result set
MAX –
Returns the maximum value of a column over a result set
Remember that functions are expressions. You
can use a function in most places where you can use a column or other
type of expression.
Examples:
SELECT sysdate FROM dual;
SELECT current_user();
SELECT TO_CHAR(hire_date, 'DD-MON-YYYY') FROM
employees;
SELECT COUNT(*) FROM employees;
SELECT department_id, SUM(salary),
AVG(salary), MAX(salary)
FROM employees
GROUP BY department_id;
See
Appendix B for a cross reference of SQL functions by database vendor.
Chapter
4, section “Calling SQL Functions” provides examples of
calling functions within a select statement.
Topics:
