NVL(x, y)where both x and y are expressions. This function returns y, if x is NULL. If x is not NULL, it returns x.
NVL2(x, y, z)where x, y, and z are expressions. This function returns z if x is NULL, and y if x is not NULL. This function allows us to implement an IF...THEN...ELSE construct based on the nullity of data.
Function | Description | Example |
---|---|---|
ASCII(s) | Returns the ASCII code of the first character of the string s. | |
CHR(i) | Returns the character with the ASCII code i. | CHR(116) = 't' |
CONCAT(s1, s2) | Returns string s2 appended to s1. | |
INITCAP(s) | Returns the string s with the first character of each word in uppercase and all others in lowercase. | INITCAP('hello SIR') = 'Hello Sir' |
Returns the numeric character position in s where the n-th occurrence of sub is found. The search begins at the position pos in s. If the substring not found, it returns 0. If the pos is negative, the search is performed backwards (from right to left). The default values for pos and n are 1. | INSTR('test', 't', 2) = 4 | |
LENGTH(s) | Returns the number of characters in the string s. | LENGTH('Welcome Sir') = 11 |
LOWER(s) | Returns the string s with all characters in lowercase. | LOWER('Welcome Sir') = 'welcome sir' |
LPAD(s, i[, s1]) | Returns the string s expanded in length to i characters, using s1 to fill in space as needed on the left side. | LPAD('test', 11, '*-') = '*-*-*-*test' |
LTRIM(s, s1) | Returns s without any leading character that appear in s1. If no s1 character are leading characters in s, then s is returned unchanged. | LTRIM('welcome', 'slow') = 'elcome' |
RPAD(s, i[, s1]) | Returns the string s expanded in length to i characters, using s1 to fill in space as needed on the right side. | LPAD('test', 11, '*-') = 'test*-*-*-*' |
RTRIM(s, s1) | Returns s without any trailing character that appear in s1. If no s1 character are trailing characters in s, then s is returned unchanged. | RTRIM('Mississippi', 'pi') = 'Mississ' |
REPLACE(s, s1[, s2]) | Returns s with all occurrences of substring s1 replaced with s2. By default s2 is NULL and all occurrences of s1 are removed. | REPLACE('www.yahoo.com', 'yahoo', 'google') = 'www.google.com' |
SUBSTR(s, pos[, len]) | Returns the portion of the string s that is len characters long, beginning at position pos. If pos is negative, the position is counted backwards from the end of the string. The function returns NULL if len is less or equal to zero. If len is skipped, it returns the remaining of s. | SUBSTR('welcome', 4) = 'come' |
SOUNDEX(s) | Returns the soundex phonetic representation of the string s. It's useful when we need to find words that sound like a given one. | SOUNDEX('John') = J500 = |
TRANSLATE(s, s1, s2) | Returns the string s with all occurrences of characters in s1 replaced with the positionally corresponding characters is s2.If s2 is shorter than s1, the unmatched characters in s1 are removed. | TRANSLATE('alfabet', 'abscde', 'BCDE') = 'BlfBCt' |
TRIM([s1 kw FROM] s) | This function returns the string s with all s1 (leading, trailing, or both) occurrences of characters in s removed. If present kw is one of the following LEADING, TRAILING, or BOTH (by default it's BOTH). The default value of s1 is a space character. | TRIM(BOTH '.' FROM 'etc ...') = 'etc ' |
UPPER(s) | Returns the string s with all characters in uppercase. | UPPER('Welcome Sir') = 'WELCOME SIR' |
Function | Description |
---|---|
ABS(d) | Returns the absolute value of the double d. |
ACOS(d) | Returns the arc cosine of the value d expressed in radians (d should be between -1 and 1). |
ASIN(d) | Returns the arc sine of the value d expressed in radians (d should be between -1 and 1). |
ATAN(d) | Returns the arc tangent of the value d expressed in radians. |
ATAN2(d1, d2) | Returns the arc tangent of the value d1/d2 expressed in radians. |
BITAND(i1, i2) | Returns the bitwise AND operation performed on two integer arguments i1 and i2. The result is also an integer. |
CEIL(d) | Returns the smallest integer that is greater or equal to d. |
COS(d) | Evaluates the cosine of d radians. |
COSH(d) | Returns the hyperbolic cosine of d. |
EXP(d) | Returns ed |
FLOOR(d) | Returns the largest integer less or equal to d. |
LN(d) | Returns the natural logarithm of d. |
LOG(b, d) | Returns logbd. |
MOD(i1, i2) | Returns i1 modulo i2, or the reminder of i1 divided i2. |
POWER(d, p) | Returns d to the pth power (dp). |
ROUND(d, i) | Returns d rounded to i digits of precision to the right of the decimal point. If i is negative, d is rounded to the left of the decimal point. |
SIGN(d) | Returns -1 if d is negative, 1 if d is positive, and 0 if d is zero. |
SIN(d) | Evaluates the sine of d radians. |
SINH(d) | Returns the hyperbolic sine of d. |
SQRT(d) | Returns the square root of d. |
TAN(d) | Evaluates the tangent of d radians. |
TANH(d) | Returns the hyperbolic tangent of d. |
TRUNC(d, i) | Returns d truncated to i digits of precision to the right of the decimal point. If i is negative, d is truncated to the left of the decimal point. |
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
Function | Description | Example | |||
---|---|---|---|---|---|
ADD_MONTHS(dt, i) | Returns the date dt plus i months. If i is a decimal number. Oracle will automatically convert it to an integer by truncating the decimal portion (it may also be negative). | ADD_MONTHS(SYSDATE, 2) = '01-SEP-2003 12:00:03' | |||
CURRENT_DATE | Returns the current date in the Gregorian calendar for the session's time zone. It requires no arguments. | CURRENT_DATE = '01-JUL-2003 12:00:35' | |||
CURRENT_TIMESTAMP([p]) | Returns the current date and time in the session's time zone to p digits of the precision, p should be an integer 0 through 9 and defaults to 6. The return datatype is TIMESTAMP WITH TIME ZONE. (See also LOCALTIMESTAMP) | CURRENT_TIMESTAMP(4) = '01-JUL-03 12:00:51.8261 PM -04:00' | |||
DBTIMEZONE | Returns the database's time zone, as set by the latest CREATE DATABASE or ALTER DATABASE SET TIME_ZONE statement. The time zone is a string specifying the hours and minutes offset from UTC (Coordinated Universal Timezone, aka as GMT, or Greenwich Mean Time) or a time zone regional name. The valid time zone regional name can be found in the TZNAME column of the view V$TIMEZONE_NAMES | DBTIMEZONE = '-07:00' | |||
EXTRACT(c FROM dt) | Returns the component specified by c from the date/time or interval dt. The valid components
are:
|
EXTRACT(MONTH FROM SYSDATE) = 7 | |||
FROM_TZ(ts, tz) | Returns a timestamp with time zone for the timestamp ts using the time zone value tz. The string tz specifies the hours and minutes offset from UTC or is a time zone region name. | FROM_TZ(LOCALTIMESTAMP, '+3:00') = '01-JUL-03 12:01:57.679850 PM +03:00' | |||
LAST_DAY(dt) | Returns the last day of the month for the date dt. | LAST_DAY('25-Feb-2004') = '29-FEB-2004 00:00:00' | |||
LOCALTIMESTAMP([p]) | Returns the current date and time in the session's time zone to p digits of the precision, p should be an integer 0 through 9 and defaults to 6. This function is similar to the CURRENT_TIMESTAMP, but the return datatype is TIMESTAMP. | LOCALTIMESTAMP(2) = '01-JUL-03 12:02:48.59 PM' | |||
Returns the number of months that dt1 is later than dt2. A whole number is returned if dt1 and dt2 are the same day of the month or if both are th last day of the month. | |||||
NEW_TIME(dt, tz1, tz2) | Returns the date in time zone tz2 for the date dt in time zone tz1. See time zone constants | NEW_TIME(SYSDATE, 'EST', 'PDT') = '01-JUL-2003 10:04:47' | |||
NEXT_DAY(dt, s) | Returns the date that corresponds to the next day of week specified by the string s following the date dt. The time portion of the date is the same as the time portion of dt. The string s should be a text string containing the full or abbreviated day of the week in the session's language. | NEXT_DAY(SYSDATE, 'Mon') = '07-JUL-2003 12:05:13' | |||
ROUND(dt[, fmt]) | Returns the date dt rounded to the granularity specified in the date-format string fmt. See date format codes. | ROUND(SYSDATE, 'HH24') = '01-JUL-2003 11:00:00' | |||
SESSIONTIMEZONE | Returns the database's time zone as per the last LATER SESSION statement. It takes no arguments. | SESSIONTIMEZONE = '-04:00' | |||
SYS_EXTRACT_UTC(ts) | Returns the UTC (GMT) time zone from the timestamp ts. |
CURRENT_TIMESTAMP = '01-JUL-03 11.48.19.618428 AM -04:00' SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) = '01-JUL-03 03:47:05.917078 PM' |
|||
SYSDATE | Returns the current date/time, takes no arguments. | SYSDATE = '01-JUL-2003 11:50:08' | |||
SYSTIMESTAMP | Takes no arguments and returns a TIMESTAMP WITH TIME ZONE for the current database date and time. The fractional second is returned with six digits of precision. | SYSTIMESTAMP = '01-JUL-03 11:52:10.051237 AM -04:00' | |||
TRUNC(dt[, fmt]) | Returns the date dt truncated to the granularity specified by the format string fmt. | TRUNC(SYSdate, 'DD') = '01-JUL-2003 00:00:00' | |||
TZ_OFFSET(tz) | Returns the numeric time zone offset for the textual time zone name tz. | TZ_OFFSET('US/Eastern') = '-04:00' |
Function | Description |
---|---|
ASCIISTR(s) | Returns the ASCII equivalent of all characters in the string s. |
BIN_TO_NUM(bit_list) | Takes a single argument bit_list - the comma-delimited list of bits, and returns the numeric representation of all the bit-field set bit_list. |
CAST(expr AS type) | Converts the expression expr into datatype type. The expr can be an expression, subquery, or MULTISET clause. See the possible conversion table for standard datatypes. |
CHARTOROWID(s) | Returns the string s as a ROWID datatype. |
COMPOSE(s) | Returns the string s as a Unicode string in its fully normalized form, in the same characters set as s. |
Returns the string s converted from the soursecharacter set sset to the destination character set dset. | |
DECOMPOSE(s) | Returns the string s as a Unicode string after canonical decomposition in the same character set as s. |
HEXTORAW(x) | Returns the hexadecimal string x converted to a RAW datatype. |
NUMTODSINTERVAL(x, st) | Converts the number x into an INTERVAL DAY TO SECOND datatype accordingly to the string st denoting the units for x. Valid units are: 'DAY', 'HOUR', 'MINUTE', and 'SECOND'. |
NUMTOYMINTERVAL(x, st) | Converts the number x into an INTERVAL YEAR TO MONTH datatype accordingly to the string st denoting the units for x. Valid units are: 'YEAR' and 'MONTH'. |
RAWTOHEX(rs) | Returns the raw string rs converted to hexadecimal. |
ROWIDTOCHAR(x) | Returns the string x (in the format of ROWID) converted from ROWID. |
TO_CHAR(x[, fmt[, nls]]) | Takes up to three arguments, where x is either a date or a number, fmt is
a format string specifying the format that x will appear in, and nls specifies language or
location formatting string.
If x is a date, fmt is a date format code. If x is a number, fmt is a numeric format code. |
TO_DATE(s[, fmt[, nls]]) | Converts string s to DATE datatype accordingly the format string fmt. The fmt string uses the same date format code. The default is the value stored in the NLS_DATE_FORMAT session variable. |
TO_DSINTERVAL(s[, nls]) | Returns the string s converted into an INTERVAL DAY TO SECOND datatype. |
TO_NUMBER(s[, fmt[, nls]]) | Returns the numeric value represented by the string s. The format string fmt specifies the format the that s appears in. |
TO_YMINTERVAL(s) | Returns the string s converted into an INTERVAL YEAR TO MONTH datatype. |
UNISTR(s) | Returns the string s in Unicode in the database Unicode character set. |
Function | Description |
---|---|
BFILENAME(dir, file) | Takes two arguments, where dir is a string containing a directory name and file is a string containing a file name. Returns an empty BFILE locator. When used, the BFILE is instantiated. Neither dir nor file needs to exist at the time the function is called, but both must exist when the locator is used. |
COALESCE(expr_list) | Returns the first non-NULL value in the expr_list. If all expressions are NULL, then NULL is returned. Each expression in the expr_list should be the same type. |
DECODE(x, v1, r1[, v2, r2 ...][, d]) | The function compares the value of the expression x with values of the arguments v1, v2,
etc. If the value of x is equivalent to v1, then r1 is returned; otherwise the additionalmatching
expressions are compared. If no match is found and the default expression d is included, then the d
is returned. If no match is found and there is no d argument, then NULL is returned.
Example: SELECT sid, serial#, username, DECODE(command, 0, 'None', 2, 'Instert', 3, 'Select', 6, 'Update', 7, 'Delete', 8, 'Drop', 'Other') cmd FROM v$session WHERE type <> 'BACKGROUND'; |
DUMP(x[, fmt[, sb[, len]]]) | Takes up to four arguments, where x is an expression, fmt is a format specification for
, sb is the starting byte offset within x, and len is the length in bytes to dump. The function returns a character string containing the datatype of x in numeric notation and the internal presentation of x. |
EMPTY_BLOB | Returns an empty BLOB locator. This function is used to initialize a BLOB variable or BLOB column in a table. |
EMPTY_CLOB | Returns an empty CLOB locator. This function is used to initialize a CLOB variable or CLOB column in a table. |
GREATEST(expr_list) | Returns the expression from the expr_list that sorts the highest in the datatype of the first expression. If the expression list contains a NULL, then a NULL is returned. |
LEAST(expr_list) | Returns the expression from the expr_list that sorts the lowest in the datatype of the first expression. If the expression list contains a NULL, then a NULL is returned. |
NULLIF(x1, x2) | Returns NULL if the expression x1 is equal to x2; otherwise returns x1. |
SYS_CONNECT_BY_PATH(col, chr) | This function works only with hierarchical queries - queries that use CONNECT BY clause.
It returns the path of column col, delimited by the character chr, from root to node for each row
returned by the CONNECT BY condition.
Example: SELECT dep_id, dep_name, SYS_CONNECT_BY_PATH(dep_name, '-') 'Dep. Chain' FROM departments START WITH dep_id = 0 CONNECT BY PRIOR dep_id = sup_dep_id; |
SYS_CONTEXT(ns, par[, len]) | Takes up to three arguments, where ns is a namespace, par is a parameter associated with the namespace ns, and len is the length of the return value in bytes (default 256). The built-in namespace USERENV contains information on the current session. See the table with parameters in the USERENV |
UID | Returns the integer user ID for the current user. |
USER | Returns a character string containing the username for the current user. |
USERENV(opt) | Takes a single argument, where opt is one of the following options:
This function is deprecated in release Oracle9i. |
VSIZE(x) | Returns the size in bytes of the internal representation of the expression x. |
www.BZUPAGES.com