System Functions

The decode() Function

Example:
Select
decode(AUTHORIZATION_STATUS,
       ‘APPROVED’, ‘APPRED’,
       ‘IN PROCESS’, ‘JUST STARTED’,
       ‘Others’)
From PO_HEADERS_ALL
SYSDATE
In Oracle/PLSQL, the sysdate function returns the current system date and time on your local database.
The syntax for the sysdate function is:    sysdate
Example #1:
Select SYSDATE FROM DUAL;
OUTPUT: 3/3/2009 2:22:27 AM

Example #2:
Select TRUNC(SYSDATE) FROM DUAL;
OUTPUT: 3/3/2009

Example #3:
Select TO_CHAR(SYSDATE,’DD-MM-YYYY’) FROM DUAL;
OUTPUT: 03-03-2009
Example #4:
select sysdate into v_date from dual;
OUTPUT: The variable called v_date will now contain the current date and time value.

Example #5:
You could also use the sysdate function in any SQL statement. For example:
    select supplier_id, sysdate
    from suppliers
    where supplier_id > 5000;
Arithmetic Functions
Other functions are designed to perform specialized mathematical functions, such as those used in scientific applications such as sine and logarithms. These operations are commonly referred to as arithmetic or number operations. The functions falling into this category are listed next. These functions are not all that is available in Oracle, but rather they are the most commonly used ones that will likely appear on OCP Exam 1: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. For example, round(134.345,1) = 134.3, round(134.345,0) = 134, round(134.345,-1) = 130. This can also be used on DATE columns.
ceil(x) Similar to executing round on an integer (for example, round(x,0)), except ceil always rounds up. For example, ceil(1.4) = 2. Note that rounding up on negative numbers produces a value closer to zero (for example, ceil(-1.6) = -1, not -2).
floor(x) Similar to ceil, except floor always rounds down. For example, floor(1.6) = 1. Note that rounding down on negative numbers produces a value further away from zero (for example, floor (-1.6) = -2, not -1).
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. For example, mod(10,3) = 1, and mod(10,2) = 0.
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. This can also be used on DATE columns.
vsize(x) The storage size in bytes for value x.
Text Functions
Several functions in Oracle manipulate text strings. These functions are similar in concept to nvl() and decode() in that they can perform a change on a piece of data, but the functions in this family can change only VARCHAR2 and CHAR data. Here are some examples:   
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 position z of the string. For example, substr(‘ABCDEFG’,3,4) returns CDEF.
    
instr(x,y) Determines whether a substring y given can be found in string x. For example, instr(‘CORPORATE FLOOR’,’OR’) returns 2.
The trim() Function A single-row function called trim() behaves like a combination of ltrim() and rtrim(). The trim() function 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. Note that trim() only removes trailing or leading instances of the character specified. If that character appears somewhere in the string, trim() will not remove it.
Conversion Functions
Conversion functions are designed to convert data from one datatype format to another. These functions do not actually modify the stored data in the table itself; they just return the converted values to the SQL*Plus session. Figure 2-1 displays how information can get converted from one datatype to another using various functions. Several different conversion functions are available in the Oracle database, as listed here:
to_char(x) Converts the value x to a character or converts a date to a character string using formatting conventions (see “Date-Formatting Conventions” subtopic below).
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.
hextoraw(x) Converts the hexadecimal (base-16) value x into a raw (binary) format.
rawtohex(x) Converts the raw (binary) value x into a hexadecimal (base-16) format.
convert(x[,y[,z]]) Executes a conversion of alphanumeric string x from the current character set (optionally specified as z) to the one specified by y.
translate(x,y,z) Executes a simple value conversion for character or numeric string x into something else based on the conversion factors y and z.