Oracle Functions

ORACLE FUNCTIONS

·         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)  : It returns a char value representing the sound of the words 

·         SQRT(n)   :   to find  Square Root of a number

                                  Eg : sqrt(4)=2 

·         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 

·         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

                               Eg :to_char(to_date(‘1/sep/2009’),’MON’)  =sep 

·         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.  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 or output datatype  is NCHAR or 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 or 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 

·         VARIANCE([DISTINCT] n) : Variance of n, ignoring NULLs

·         VSIZE(expr)  :  Value Size - returns the number of bytes used by each row of expr.