Functions

SQL functions are broken down into character functions, number functions, and date functions. Be sure you know how to use these functions for the Oracle Certified Partner (OCP) exam:

Text Functions

• lpad(x,y[,z]) and rpad(x,y[,z]) Return data in string or column x padded on the left or right side, respectively, to width y. The optional value z indicates the character(s) that lpad() or rpad() use to pad the column data. If no character z is specified, a space is used.
• lower(x), upper(x), and initcap(x) Return data in string or column x in lowercase or uppercase characters, respectively, or change the initial letter in the data from column x to a capital letter.
• length(x) Returns the number of characters in string or column x.
• substr(x,y[,z]) Returns a substring of string or column x, starting at the character in position number y to the end, which is optionally defined by the character appearing in the position z of the string.
• instr(x,y) Determines whether a substring y given can be found in string x.
• trim() A single-row function that behaves like a combination of ltrim() and rtrim(). Trim() accepts a string describing the data you would like to trim from a column value using the following syntax: trim([[keyword] 'x' from] column). Here keyword is replaced by leading, trailing, or both, or it's omitted. Also, x is replaced with the character to be trimmed, or it's omitted. If x is omitted, Oracle assumes it must trim whitespace. Finally, column is the name of the column in the table to be trimmed.

Arithmetic Functions

• abs(x) Obtains the absolute value for a number. For example, the absolute value of –1 is 1, whereas the absolute value of 6 is 6.
• round(x,y) Rounds x to the decimal precision of y. if y is negative, it rounds to the precision of y places to the left of the decimal point. This can also be used on DATE columns.
• ceil(x) Similar to executing round on an integer, except ceil always rounds up.
• floor(x) Similar to ceil, except floor always rounds down.
• mod(x,y) The modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced.
• sign(x) Displays an integer value corresponding to the sign of x: 1 if x is positive, -1 if x is negative.
• sqrt(x) The square root of x.
• trunc(x,y) Truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point.
• vsize(x) The storage size in bytes for x.

List Functions

• greatest(x,y,…) Returns the highest value from the list of text, strings, numbers or dates.
• least(x,y,…) Returns the lowest value from the list of text strings, numbers, or dates.
• decode(column name, val1, sub1, val2, sub2,…) Works on the same principle as the if-then-else statement does in many common programming languages.

Date Functions

• add_months(x,y) Returns a date corresponding to date x plus y months.
• last_day(x) Returns the date of the last day of the month that contains date x.
• months_between(x,y) Returns a number of months between dates x and y. If date x is earlier than y, the result is negative; otherwise, the result is positive. If dates x and y contain the same day of different months, the result is an integer; otherwise, the result is a decimal.
• new_time(x,y,z) Returns the current date and time for date x in time zone y as it would be in time zone z.
• next_day(x) Identifies the name of the next day from given date, x.

Several conversion functions are available for transforming data from text to numeric datatypes and back, numbers to dates and back, text to ROWID and back, and so on.

Conversion Functions

• to_char(x) Converts the value x to a character or converts a date to a character string using formatting conventions.
• to_number(x) Converts nonnumeric value x to a number.
• to_date(x[,y]) Converts the nondate value x to a date using the format specified by y.
• to_multi_byte(x) Converts the single-byte character string x to multibyte characters according to national language standards.
• to_single_byte(x) Converts the multibyte character string x to single-byte characters according to national language standards.
• chartorowid(x) Converts the string of characters x into an Oracle ROWID.
• rowidtochar(x) Converts the ROWID value into the string of characters x of VARCHAR2 datatype.