SQL Functions
SQL provides includes several built-in functions. SQL functions fall into two categories - column functions and scalar functions. Column functions have column names as their parameters; scalar functions operate on a single value. All SQL functions return a single value. The format of a function is:
function-name(parameters)
An SQL function can be used wherever an expression is allowed in an
SQL
command.
Column or Group Functions
The SQL column functions are AVG, COUNT, MAX, MIN, STDDEV, SUM and VARIANCE. When used in a SELECT list, these entries must not be mixed with single valued entries; that is, if a column built-in function appears in the select list, all entries must be column functions. The exception is when GROUP BY is specified.
Most of the functions allow the option DISTINCT - only distinct values are used in the calculation.
AVG - calculates the average of the values in the column
COUNT - counts the number of items
MAX - finds the maximum value in the column
MIN - finds the minimum value in the column
STDDEV - calculates the standard deviation
SUM - calculates the total of the column entries
VARIANCE - calculates the variance
SQL ignores columns containing NULL values.
Examples:
To display the average price of shares in the STOCK table:
select avg(currentprice) from stock;
To display the largest salary paid to a manager:
select max(salary) from manager;
To display the number of different stocks purchased on October 6, 1999:
select count(distinct stocksymbol) from holding where purchasedate = '06-Oct-1999';
The following example illustrates the use of the GROUP BY clause; the total shares in each portfolio in the HOLDING table is displayed:
select portfoliono,sum(numbershares) from holding group by portfoliono;
The following will by displayed:
PORT SUM(NUMBERSHARES)
---- -----------------
P001 1750
P004 400
P005 2300
P008 2410
To display the portfolio number of holdings that have an average number of shares more than 200, the GROUP BY and HAVING clauses can be used:
select portfoliono from holding group by
portfoliono having avg(shares) > 200;
Scalar Functions
A scalar function is a function that is applied to a single value as opposed to column functions that operate on a column of values. Scalar functions can be used wherever an expression is allowed. Some of the scalar functions available are described below.
Numeric Functions
The numeric functions include:
MOD(m,n): returns the remainder of m divided by n
POWER(m,n): returns m raised to the nth power
ROUND(n,m): returns n rounded to m decimal placesSQRT(n): returns the square root of n
TRUNC(m,n): returns m truncated to n decimal places
Character Functions
There are two types of character functions: case manipulation functions and character manipulation functions. In the following examples, c is assumed to be a character data item.
Case Manipulation
Functions:
LOWER(c): returns c with all characters converted to lower case
INITCAP(c):
returns c with the first letter of each
word converted to uppercase and the remaining letters to lowercase.
UPPER(c): returns c with all characters converted to upper case
Character Manipulation
Functions:
CONCAT(c,b): returns a character string with values of c and b joined together
INSTR(c,b): returns the
numeric position of a named character b within a character string c
LENGTH(c): returns the length of c
LPAD(c,n,' '): pads
the value in c with n ' ' characters right
justified
LTRIM(c): returns c with all leading (leftmost) spaces removed
REPLACE(c, search-string[,replace-string]): returns c with all occurrences of search-string replaced with the string replace-string; if replace-string omitted, all occurrences of search-string are removed from c
RPAD(c,n,' '): pads
the value in c with n ' ' characters right
justified
RTRIM(c): returns c with all trailing (rightmost) spaces removed
SUBSTR(c, m [,n]): returns a substring of c, starting at the mth character and n characters in length; if n is omitted, the substring extends to the end of c
TRIM(c):
returns c with heading or trailing
characters (or both) spaces removed
Date Functions
ADD_MONTHS(date, count): adds the number of months specified in count to the value in date
LAST_DAY(date): returns the date of the last day of the month in which the given date falls
MONTHS_BETWEEN(date1,
date2): number of months between two
dates.
NEXT_DAY(date,'
c'):
returns the next date of the day specified by c (number or character
string)
ROUND(date,[, 'fmt' ]) – rounded to specified format (if no format, round to nearest day)
SYSDATE: returns the current system date (on the server)
TRUNC(date,[, 'fmt' ]) – returns date with time truncated to unit specified in format (if no format, round to nearest day)
Additional Date/Time functions:
Current_Date: current
date and time in session’s time
zone
Current_Timestamp: current
date and fractional time in session’s time zone
DBTimeZone: Operating
System’s time Zone
LocalTimestamp: date and
time in session’s time zone with timestamp value
SessionTimeZone: current
session timezone
TZ_offset: returns zone
offset from UTC(Coordinated Universal time)
was GMT
(Greenwich Mean Time)
From_TZ: converts
timestamp value to Timestamp with time zone value
To_Timestamp: string to
timestamp
To-Timestamp_TZ:
string to
timestamp with time zone
Conversion Functions
It is
recommended that you explicitly perform
conversions. However, Oracle is able to
implicitly handle some conversions. TO_CHAR, TO_NUMBER and TO_DATE are
the main
conversion functions.
TO_CHAR(date, format): this function converts a date to the specified format; the format can be any valid date format.
Examples:
to_char(sysdate,'dd-Mon-yyyy') would return the current date with a four digit year
to_char(sysdate, mm)
would
return the current month
TO_CHAR(number, format): this format of the TO_CHAR function converts a numeric item to character using the specified format.
Example:
to_char(1653.78,
'$9999.9')
would return a character data item containing $1653.8.
TO_DATE(char,
format): returns
a date from a character string in date format.
TO_NUMBER(char,
format):
returns a number from a character string of digits.
|
Date Formats |
Number Formats |
|
YEAR – Year spelled out |
9-represents a number |
|
MONTH – Full name of month |
0-forces 0 to be displayed |
|
MON-3 letter abbreviation for month |
$-Floating dollar sign |
|
DY – 3 letter abbreviation for day of week |
L-Local currency |
|
DAY – full name of week day |
. – decimal point |
|
DD-Numeric day of month |
, - thousands indicator |
For
Example: Display holding details,
including the purchase date and the first Monday six months after the
purchase
of the holding:
Select
portfoliono, stocksymbol, purchaseDate,
To_char(next_day(Add_months(purchaseDate,6),
'Monday '), 'fm Day, " the " Ddspth "of " Month, YYYY ')
As
"Review Purchase "
From
Holding;
RR-Date Format
(DD-MON-RR)
|
Current Year |
Specified Date |
RR Format |
YY Format |
|
2005 |
20-AUG-14 |
2014 |
2014 |
|
2005 |
20-AUG-95 |
1995 |
2095 |
|
2060 |
20-AUG-14 |
2114 |
2014 |
|
2060 |
20-AUG-95 |
2095 |
2095 |
|
Current Year |
Specified Year |
|
|
Range |
0-49 |
50-99 |
|
0-49 |
Current century |
Current century -1 |
|
50-99 |
Current century + 1 |
Current century |
General Functions
NVL(exp1,
exp2): returns exp2 if exp1 is null (example:
NVL(salary, 0) required before arithmetic on salary)
NVL2(exp1,exp2,exp3):
if exp1 is not null exp2 is returned, else
exp3 is returned
NULLIF(exp1,exp2):
if exp1=exp2 returns null, else returns exp1
COALESCE(exp1,exp2,…expn):
returns the first non-null expression
in the list
CASE exp WHEN
compare-exp1 THEN
return-exp1
[WHEN compare-exp2 THEN return-exp2
WHEN compare-expn THEN
return-expn
ELSE exp2]
END
Example:
Select
constraint_name, case
constraint_type WHEN 'R' THEN 'Referential'
when 'P' then 'Primary Key'
when 'C' then 'Check'
else 'unknown'
END
from
user_constraints
where
table_name='MANAGER';