1. Case Conversion Functions
- The LOWER Function
Replaces the uppercase characters in a string with their lowercase equivalents. LOWER(s)
- The UPPER Function
Replaces the lowercase characters in a given string with their uppercase equivalents.UPPER(s)
- The INITCAP Function
Converts a string of characters into capitalized case. INITCAP(s)
2. Character Manipulations Functions
- The CONCAT Function
Joins two character literals, columns, or expressions to yield one larger character expression.CONCAT(s1,s2)
- The LENGTH Function
Returns the number of characters that constitute a character string.LENGTH(s)
- The LPAD and RPAD Functions
Return a string padded with a specified number of characters to the left or right of a given string respectively.LPAD(s,n,p) RPAD(s,n,p)
- The TRIM Function
Removes characters from the beginning or end of character values to yield a potentially shorter item.TRIM([trailing|leading|both]trimstring froms)
- The INSTR Function
Locates the position of a search string within a given string. INSTR(source string, search string, [search start position], [nth occurrence])
- The SUBSTR Function
Extracts a substring of a sepcified length from the source string beginning at a given position.SUBSTR(source string, start position, [number of characters to extract])
- The REPLACE Function
Replaces all occurrences of a search item in a source string with a replacement term.REPLACE(source string, search item, [replacement term])
3. Numeric Functions
- The Numeric ROUND Function
Performs a rounding operation on a numeric value based on the decimal precision specified.ROUND(source number, decimal precision)
- The Numeric TRUNC Function
Performs a truncation operation on a numeric value based on the decimal precision specified, drops the numbers beyond the decimal precision specified, does not attempt to round up or down.TRUNC(source number, decimal precision)
- The MOD Function
Returns the numeric remainder of a division operation. MOD(dividend, divisor)
4. Date Functions
- The SYSDATE Function
Returns the current system date(DD-MON-RR) and time according to the database server.
- The MONTHS_BETWEEN Function
Returns the number of months(31-day) between two mandatory date parameters. MONTHS_BETWEEN(date1, date2)
- The ADD_MONTHS Function
Returns a date item calculated by adding a specified number of months to a given date value.ADD_MONTHS(start date, number of months)
- The NEXT_DAY Function
Returns the date when the next occurrence of a specified day of the week occurs.NEXT_DAY(start date, day of the week)
- The LAST_DAY Function
Returns the date of the last day in the month to which the given day belongs.LAST_DAY(start date)
- The Date ROUND Function
Performs a rounding operation on a value based on a specified date precision format.ROUND(source date, [date precision format])
The date precision formats include century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH), and minute (MI).
- The Date TRUNC Function
Performs a truncation operation on a date value based on a specified date precision format. TRUNC(source date, [date precision format])
二. Group Functions
1. Group Functions
- The COUNT Function
COUNT(*|[DISTINCT|ALL] expr) ;
- The SUM Function
SUM([DISTINCT|ALL] expr) ;
- The AVG Function
AVG([DISTINCT|ALL] expr) ;
- The MAX and MIN Function
MAX([DISTINCT|ALL] expr) ;
MIN([DISTINCT|ALL] expr) ;
2. Group Data Using the GROUP BY Clause
SELECT column|expression|group_function(column|expression [alias]),...}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
3. Nested Group Functions
G1(group_item) = result
G1(G2(group_item ) = result
G1(G2(G3(group_item))) is NOT allowed.
4. Include or Exclude Grouped Rows Using the HAVING Clause
SELECT column|expression|group_function(column|expression [alias]),...}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[HAVING group_condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];