ABS(n) Absolute value of number
ACOS(n) arc cosine of n
ADD_MONTHS(date,num_months) Returns date + num_months
ASCII(char) Converts char into a decimal ascii code
ASIN(n) arc sine of n.
ATAN(n) arc tangent of n.
ATAN2(n.m) arc tangent of n & m.
AVG([DISTINCT]n) Averge value of 'n' ignoring NULLs
BETWEEN value AND value Where 'x' between 25 AND 100
BFILENAME('directory','filename') Get the BFILE locator associated with a physical LOB binary file.
CASE Group the data into sub-sets.
CEIL(n) Round n up to next whole number.
CHARTOROWID(char) Converts a Char into a rowid value.
CHR(n) Character with value n
CONCAT(s1,s2) Concatenate string1 & string2
CONVERT(char_to_convert, new_char_set, old_char_set) Convert character sets
COS(n) Cosine of number
COSH(n) Hyperbolic Cosine of number
COUNT(*) Count the no of rows returned
COUNT([DISTINCT] expr) Count the no of rows returned by expr
DECODE IF x THEN return y ELSE return z
DENSE_RANK Calculate the rank of a value in a group
DEREF(e) Return the object reference of argument e.
DUMP(expr,fmt[,start,length]) Convert to dec/hex/oct & display char set
EMPTY_BLOB Return an empty LOB locator (use to empty a column / variable)
EMPTY_CLOB Return an empty LOB locator (use to empty a column / variable)
EXISTS Return TRUE if a subquery returns at least one row
EXP(n) Exponential (e to 'n'th power)
FLOOR(n) Round n down to the next whole number.
GREATEST(expression, expression...) Returns the largest in a list of expressions.
GROUPING Grouping for super aggregate rows=NULL
HEXTORAW(char) Convert char containing hex digits to a raw value.
IN (list of comma separated values) Effectively a shorthand for ['x' = y / 'x' = z...] i.e.
Where 'x' IN ('sales','marketing','recruitment')
INITCAP(char) String with Initial Capitals
INSTR(str, chars[,s[,n]]) Find the 'n'th occurence of 'chars' in 'str' Starting at position 's' n & s default to 1
INSTRB (str, chars[,s[,n]]) The same as INSTR, except that 's' & the return value are expressed in bytes, use for double-byte char sets
IS [NOT] NULL Check for NULL (empty) values Where 'x' IS NULL;
LAST_DAY(date) Returns the last day of month in Date
LEAST(expression, expression...) Returns the smallest in a list of expressions
LENGTH(char) Returns the number of characters in char
LENGTHB(char) Returns the number of bytes in char (use for double-byte char sets)
LIKE wildcard/value Wildcards are [% = any chars] [ _ = any one char]
Where 'x' LIKE 'smith%' [will find 'Smithson']
Where 'x' LIKE 'smith_' [will find 'Smithy']
LN(n) Natural Log of n, where n>0
LOG(b,n) log of n, base b
LOWER(char) Returns character string in lowercase
LPAD(char, n[,PadChar]) Left Pad char with n spaces [/ PadChars]
LTRIM(char[,set]) Left Trim char - remove leading spaces [/ char set]
MAKE_REF(table,key) Create a REF to a row of an OBJECT view/table
MAX([DISTINCT] expr) Maximum value returned by expr
MIN([DISTINCT] expr) Minimum value returned by expr
MOD(x,y) Remainder of x divided by y
MONTHS_BETWEEN(end_date, start_date)
Number of months between the 2 dates (integer)
NEW_TIME(date, zone1, zone2) Convert between GMT & US time zones (but not CET)
NEXT_DAY(date,day_of_week) '12-OCT-01','Monday' will return the next Mon after 12 Oct
NLS_CHARSET_DECL_LEN (bytecount,charset)
Returns the declaration width (no of chars) of an NCHAR column
NLS_CHARSET_ID(varchars) Returns the char set ID given a charset name
NLS_CHARSET_NAME(charset_id) Returns the char set name given a charset id
NLS_INITCAP(char[,'NLS_SORT = sort_sequence'])
Returns char in Initial Caps, using an NLS sort_sequence either the session default / specified directly
NLS_LOWER(char[,'NLS_SORT = sort_sequence'])
Returns char in lower case, using an NLS sort_sequence either the session default / specified directly
NLSSORT(char[,'NLS_SORT = sort_sequence'])
Return the string of bytes used to sort char, using an NLS sort_sequence either the session default / specified directly
NLS_UPPER(char[,'NLS_SORT = sort_sequence'])
Returns char in UPPER case, using an NLS sort_sequence either the session default / specified directly
NVL(expression, value_if_null) If expression is null, returns value_if_null; if expression is not null, returns expression. The arguments can have any datatype (Oracle will perform implicit conversion where needed).
PERCENT_RANK Calculate the percent rank of a value in a group.
POWER(m,n) m raised to the nth power
RANK Calculate the rank of a value in a group
RAWTOHEX(raw) Convert raw to a character value containing its hex equivalent
REF(table_alias) Returns a REF value for an object instance (bound to the variable / row.) The table alias (correlation variable) is associated with one row of an object table/an object view in an SQL statement.
REFTOHEX(ref) Convert ref (object type) to a char value containing its hex equivalent.
REPLACE(char, search_str[, replace_str])
ANSI alternative to decode() Replace every occurrence of search_str with replace_str, replace_str defaults to null.
ROUND(n,d) n rounded to d decimal places (d defaults to 0)
ROUND(date,fmt) date rounded to fmt
ROWIDTOCHAR(rowid) Convert a rowid value to VARCHAR2
ROW_NUMBER Assign a unique number to each row of results.
RPAD(char, n[,PadChar]) Right Pad char with n spaces [/ PadChars]
RTRIM(char[,set]) Right Trim char - remove trailing spaces [/ char set]
SIGN(n) positive = 1, zero = 0, negative = -1
SIN(n) Sine of n in Radians
SINH(n) Hyperbolic Sine of n in Radians
SOUNDEX(char) Returns a char value representing the sound of the words
SQRT(n) Square Root (returns NULL for negative no's)
STDDEV([DISTINCT] n) deviation of n
SUBSTR(char, s[,l]) A substring of char, starting at character s, length l
SUBSTRB(char, s[,l]) A substring of char, starting at character s, length l The same as SUBSTR, except that 's', 'l' & the return value are expressed in bytes, use for double-byte char sets
SUM([DISTINCT] n) Sum of values of n, ignoring NULLs SYS_CONTEXT('namespace', 'attribute_name'). Examine the package associated with the context namespace. Possible attributes are: NLS_TERRITORY, NLS_CURRENCY, NLS_CALENDAR NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, SESSION_USER, CURRENT_USER CURRENT SCHEMAID, SESSION_USERID, CURRENT_USERID, CURRENT_SCHEMA
SYS_CONTEXT ('USERENV','IP_ADDRESS')
SYS_GUID() Returns a globally unique identifier (16 byte RAW value)
SYSDATE The current system date & time
TAN(n) Tangent of n in Radians
TANH(n) Hyperbolic tangent of n in Radians
TO_CHAR Convert to character String
TO_DATE Convert to date value
TO_LOB(long) Convert LONG values to CLOB / NCLOB values/convert LONG RAW values to BLOB values. Use only as part of an "INSERT INTO ... SELECT..." subquery.
TO_MULTI_BYTE(char) Convert single-byte char to multi-byte char.
TO_NUMBER Convert to numeric format
TO_SINGLE_BYTE(char) Convert multi-byte char to single-byte char.
TRANSLATE('char','search_str','replace_str')
Replace every occurrence of search_str with replace_str unlike REPLACE() if replace_str is NULL the function returns NULL
TRANSLATE (text USING charset) Convert text into a specific character set. Use this instead of CONVERT() if either the input / output datatype is NCHAR / NVARCHAR2.
TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source)
Returns trim_source as a VARCHAR2 with leading/trailing items removed trim_char defaults to a space ' ' but may be numeric / char 'A'
TRUNC(i,d) i truncated to d decimal places (d defaults to 0)
TRUNC(date,fmt) date truncated to nearest fmt
UID User id - unique number
UPPER(char) Returns Chars in uppercase
USER Returns the current Username
USERENV('option') Can return any of the options: ENTRYID, SESSIONID, TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO
VALUE(correlation_variable) Return the object instance for a row of an object table as associated with the correlation_variable (table alias)
VARIANCE([DISTINCT] n) Variance of n, ignoring NULLs
VSIZE(expr) Value Size - returns the number of bytes used by each row of expr.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.