How-to: Oracle SQL Functions

Functions:

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 and m. 
AVG([DISTINCT]n)
              Averge value of 'n' ignoring NULLs

BETWEEN value AND value
              e.g. Where 'x' between 25 AND 100
              which means 'x' >= 25 AND 'x' <= 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 and string2
CONVERT(char_to_convert, new_char_set, old_char_set)
              Convert a  string from one character set to another.
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
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP

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 and display char set

EMPTY_BLOB    Return an empty LOB locator (use to empty a column or variable)
EMPTY_CLOB    Return an empty LOB locator (use to empty a column or variable)
EXISTS        Return TRUE if a subquery returns at least one row
EXP(n)        Exponential (e to 'n'th power)
EXTRACT       Extract part of a DATE (Year,Month,Day,Second,etc)

FLOOR(n)      Round n down to the next whole number.

GREATEST(expression, expression…)
              Returns the largest in a list of expressions.
GROUPING      Grouping for superaggregate rows=NULL
              (see GROUP BY ROLLUP/CUBE)

HEXTORAW(char) Convert char containing hex digits to a raw value.

IN (list of comma separated values)
              Effectively a shorthand for ['x' = y OR '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 and s default to 1 INSTRB (str, chars[,s[,n]]) The same as INSTR, except that 's' and the return value are expressed in bytes, use for double-byte char sets. IS NULL Check for NULL (empty) values (Select * from demo Where x IS NULL;) IS NOT NULL Check for items that contain a value (Select * from demo Where x IS NOT 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 [or PadChars] LTRIM(char[,set]) Left Trim char - remove leading spaces [or 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 MEDIAN(expr) [OVER(query partition clause) ] Returns the median of an expression (10g and above). 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 and 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 or specified directly NLS_LOWER(char[,'NLS_SORT = sort_sequence']) Returns char in lower case, using an NLS sort_sequence either the session default or 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 or specified directly NLS_UPPER(char[,'NLS_SORT = sort_sequence']) Returns char in UPPER case, using an NLS sort_sequence either the session default or 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 or row.) The table alias (correlation variable) is associated with one row of an object table or 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 [or PadChars] RTRIM(char[,set]) Right Trim char - remove trailing spaces [or 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. Use Soundex() to find names that sound alike but are spelled differently e.g. ...WHERE SOUNDEX(EMP_SURNAME) = SOUNDEX('HAUGHIE'); SQRT(n) Square Root (returns NULL for negative no's) STDDEV([DISTINCT] n) Standard 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' and 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 note: CURRENT_USER can be different from SESSION_USER within a stored procedure (e.g an invoker-rights procedure). 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_BLOB(Raw_col) Convert LONG RAW and RAW values to BLOB TO_CHAR Convert to a character String TO_CLOB Convert character or NCLOB values to the database character set. TO_DATE Convert to date value TO_LOB(long) Convert LONG values to CLOB or NCLOB values or convert LONG RAW values to BLOB values TO_MULTI_BYTE(char) Convert single-byte char to multi-byte char TO_NCHAR(expr) Convert a TEXT expression, date, or number to NTEXT in a specified format. Mostly used to format output data. TO_NCLOB Convert any character string (including LOBs) to the national character set. TO_NUMBER Convert to numeric format TO_SINGLE_BYTE(char) Convert multi-byte char to single-byte character. TO_TIME Convert to time value TO_TIME_TZ Convert to time zone TO_TIMESTAMP Convert to timestamp TO_TIMESTAMP_TZ TO_YMINTERVAL Convert a character string to an INTERVAL YEAR TO MONTH type 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 or output datatype is NCHAR or NVARCHAR2. TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source) Return trim_source as a VARCHAR2 with leading/trailing items removed trim_char defaults to a space ' ' but can be numeric or char 'A' TRUNC(i,d) Truncate i to d decimal places (d defaults to 0) TRUNC(date,fmt) Truncate Date to nearest fmt. UID User id (a unique number) UPPER(char) Return characters in uppercase USER Return 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.

Examples

Return the left 4 characters from the column prod_code, like a left() function in other languages:
SQL> select substr(prod_code,1,4) from sales;

Return the right 3 characters from the column prod_code, like a right() function in other languages:
SQL> select substr(prod_code,-3) from sales;

Return the leftmost 2 digits of idnum:
SQL> select substr(to_char(idnum),1,2) from mytable;

This page is not an exhaustive list of all the functions available - to find a complete list of functions for a particular release of Oracle see docs.oracle.com or run this query:

SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD';

“A complex system that works is invariably found to have evolved from a simple system that worked. A complex system designed from scratch never works and cannot be patched up to make it work. You have to start over with a working simple system.” ~ John Gall (Gall's Law)

 
Copyright © 1999-2024 SS64.com
Some rights reserved