POWER returns the first argument raised to the power of the second. The arguments can be a numeric value or any type that can be implicitly converted to a number. Using POWER is a good way of turning a LOG
Trim – Oracle SQL Function
TRIM returns a VARCHAR2 string with either the leading, trailing, or both the leading and trailing characters char trimmed from source. TRIM([LEADING] [TRAILING] [BOTH] char FROM SOURCE) IF you specify TRAILING then the trailing characters that match char will be
Systimestamp – Oracle SQL Function
SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE result from the underlying operating system date, timestamp, fractional seconds and time zone. Example: SELECT SYSTIMESTAMP FROM DUAL; SYSTIMESTAMP ————————————- 13-SEP-05 10.40.32.818000 PM -05:00
Remainder – Oracle SQL Function
REMAINDER returns the remainder of the 1st argument divided by the 2nd argument. Remainder is similar to MOD except thaqt REMAINDER uses ROUND in its calculations, whereas MOD uses FLOOR. Example: SELECT REMAINDER(10,3) as remainder_value from dual; REMAINDER_VALUE ——————————– 1
Upper – Oracle SQL Function
UPPER returns a VARCHAR2 string that contains all the characters of argument1 in uppercase. The argument1 can be a CHAR, VARCHAR2,NCHAR,NVARCHAR2,CLOB or NCLOB data type. Example: SELECT UPPER(‘oracle rocks’) as RESULT from DUAL; RESULT —————— ORACLE ROCKS
Asciistr – Oracle SQL Function
ASCIISTR takes a string argument and returns the ASCII equivalent. If any of the characters in the string are non ASCII, they are converted to the UTF-16xxxx format. Syntax: ASCIISTR(string) Example: SELECT ASCIISTR(‘a non-ascii char ŷ’ as RESULT FROM DUAL;
Cume_dist – Oracle SQL Function
CUME_DIST returns the relative position of a row within a group meeting certain criteria. You can specify one or more expressions to pass as arguments to the function. Syntax: CUME_DIST(expression1,… WITHIN GROUP (ORDER BY) Example: SELECT CUME_DIST(5000,103) WITHIN GROUP (ORDER
Dense_rank – Oracle SQL Function
DENSE_RANK returns a NUMBER representing the rank of a row within a group of rows. Syntax: DENSE_RANK(expression1,…) WITHIN GROUP (ORDER BY) Example: SELECT DENSE_RANK(5000,103) WITHIN GROUP (ORDER BY SALARY, MGR_ID) as RESULT FROM EMP; RESULT ———— 43
Group_id – Oracle SQL Function
GROUP_ID assigns a number to each group defined in a GROUP BY clause, GROUP_ID can be used to easily see duplicated groups in query results. Example: select avg(salary), mgr_id, group_id() gid from EMP group by mgr_id; AVG(SALARY) MGR_ID GID ———————-
Decode – Oracle SQL Function
The decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is: decode ( expression , search , result [, search , result]… [, default] ) expression is the value to compare. search is the