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;
Sign – Oracle SQL Function
SIGN returns a NUMBER that specifies the sign of the 1st argument. The possible return values are: -1 if the number is 0 -1 if the number is aBINARY_FLOAT or BINARY_DOUBLE and is = 0 or is Nan Example: SELECT
ADD_MONTHS – Oracle SQL Function
ADD_MONTHS returns a DATE which is argument1 plus num, or number of months. The argument1 can be a DATE or any type that can be implicitly converted to a DATE. The num argument can be a NUMBER, or any type
Sqrt – Oracle SQL Function
SQRT returns the square root of the argument. The argument can be a numeric value or a type that Oracle can im,plicitly convert toa numeric value. SQRT returns a value that is the same type as the supplied argument. Example:
CURRENT_DATE – Oracle SQL Function
CURRENT_DATE returns the current date Example: SELECT CURRENT_DATE as RESULT FROM DUAL; RESULT —————- 05-SEP-05
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
Max – Oracle SQL Function
MAX returns the maximum value of the expression argument. Syntax: MAX(expression) Example: SELECT max(salary) as max_salary from JOBS; max_salary ——————- 40000
Instr – Oracle SQL Function
The instr function returns the location of a substring in a string. The syntax for the instr function is: instr ( string1 , string2 , [ start_position ], [ nth_appearance ]) string1 is the string to search. string2 is the