Functions

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]];